PostgeSQL Query works as expected but doesn't output one of the list item in result set

Question

I wrote a query to find how many people from different subjects are related to trainer.

So my query should return: group_id|subject(number of users);subject(number of users);...

Where group_id is id of trainer's group and the second parameter is the main subject name and the number of users from this group per every subject.

There's some steps i've followed to make it work. Here is used one table(dm_group gr) for two facts:

1)To show to is discipline leader (gr.entity_type='Group' and gr.group_type='TRAINER'), so I can identify only teachers. In this case gr.name will be teacher's name. 2)But every user has at least one line in dm_group gr, which shows what is his the main subject/direction (gr.entity_type = 'Discipline'). In this case gr.name will be exactly the name of the main user's subject.

So, one user, as a teacher forms a group to what students can be assigned. And generally, every user has a main subject he is also assigned to. Finally, I should identify all teacher's ids with the list of all their students subjects with number of user's.

So, first of all we identify all the teachers (select gr.id where group_type='TRAINER') Next in dm_user_to_group utg we find all users assigned to each teacher(select u.id where utg.id = gr.id) So the next step is tricky, knowing all user_ids for each teacher, I have to find all group ids for this users to find in dm_group their main subject(where gr.entity_type = 'Discipline') and finally to get how much people have this or that subject as the main in each teacher's group.

Tables used:

dm_group:

create table dm_group
    (
        entity_type            varchar(15) not null,
        id                     bigserial
            primary key,
        description            text
        name                   varchar(255),
        group_type             varchar(255));

dm_user_to_group:

create table dm_user_to_group
(
    group_id bigint
        constraint fk_gr_to_u
            references dm_group,
    user_id  bigint
        constraint fk_us_to_gr
            references dm_user);

My guery:

WITH TeacherSubjects AS (
    SELECT
        tr.id AS group_id,
        COALESCE(gr.name, '') AS subject_name,
        COUNT(utg.user_id) AS num_students
    FROM
        dm_group tr
            LEFT JOIN dm_user_to_group utg ON tr.id = utg.group_id
            LEFT JOIN dm_group gr ON utg.user_id = gr.id AND gr.entity_type = 'Discipline'
    WHERE
            tr.group_type = 'TRAINER'
    GROUP BY
        tr.id,
        gr.name
)
SELECT
    ts.group_id,
    STRING_AGG(ts.subject_name || '(' || ts.num_students || ')', '; ' ORDER BY ts.subject_name) AS "Discipline Counts"
FROM
    TeacherSubjects ts
GROUP BY
    ts.group_id
ORDER BY
    ts.group_id;

It counts and identify correctly result, but not outputs subject_name even it exists in dm_group.

Please tell me what's wrong can be here..

Answer 1

You're performing the left join with dm_group to get the subject_name. The issue is likely because not all rows in dm_user_to_group have a corresponding dm_group entry with entity_type = 'Discipline', and the left join would result in NULL values for those rows. In such cases, the COALESCE(gr.name, '') expression will replace NULL values with an empty string, which is likely your issue.

The modification would be to -

WITH TeacherSubjects AS (
    SELECT
        tr.id AS group_id,
        gr.name AS subject_name,  -- Use gr.name directly
        COUNT(utg.user_id) AS num_students
    FROM
        dm_group tr
            LEFT JOIN dm_user_to_group utg ON tr.id = utg.group_id
            LEFT JOIN dm_group gr ON utg.user_id = gr.id AND gr.entity_type = 'Discipline'
    WHERE
        tr.group_type = 'TRAINER'
    GROUP BY
        tr.id,
        gr.name
)
SELECT
    ts.group_id,
    STRING_AGG(ts.subject_name || '(' || ts.num_students || ')', '; ' ORDER BY ts.subject_name) AS "Discipline Counts"
FROM
    TeacherSubjects ts
GROUP BY
    ts.group_id
ORDER BY
    ts.group_id;

Using gr.name directly in the select clause should get the correct subject name even if its not present for all rows in the left join.