Question 248994 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL Using string_agg with count

I have to create a query to show group_id and the list with subjects and number of people from this group per subject.

I have table ‘group’ with ‘entity_type’, that can define person’s subject and with ‘group_type’ that defines in which group can be this user.

Entity type can be ‘Group’ so it will be related with user, who conducts group and to who users can be assigned. In this case we will get name of user who is the chief if group.

If entity type is ‘Discipline’ then we are talking about user’s discipline so in this case we will get his discipline name.

All groups user is in can be found in another table ‘user_to_group’

Result should be like: group_id| subject(#);subject(#);…, where subject is the number of people from group and related to concrete subject.

I wrote smth like this:

*I know that string_agg doesn’t work together with count but all the solutions I found didn’t work for me.

SELECT gr.id                                                        ,
       string_agg(d.name || '(' || COUNT(DISTINCT u.userid) || ')', '; ') AS "Discipline Counts"
FROM dm_group gr
         LEFT JOIN user_to_group utg ON groups.id = utg.group_id
         LEFT JOIN dm_user u ON utg.user_id = u.id
         LEFT JOIN dm_group d ON u.id = d.id AND d.entity_type = 'Discipline'
WHERE gr.group_type = 'Math'
GROUP BY gr.id, d.name
ORDER BY gr.id;

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);
Tags:

2

Answers


  1. 0

    By combining subqueries and the string_agg function, you can get the desired results. However, you must modify your SQL query in a few specific ways. Here is an illustration of how to accomplish it:

    SELECT
    gr.id AS group_id,
    string_agg(d.name || '(' || COALESCE(CAST(sub.count AS text), '0') || ')', '; ') AS "Discipline Counts"
FROM
    dm_group gr
LEFT JOIN (
    SELECT
        g.id AS group_id,
        u.id AS user_id,
        d.name AS discipline_name
    FROM
        dm_user_to_group utg
    JOIN
        dm_group g ON utg.group_id = g.id
    JOIN
        dm_user u ON utg.user_id = u.id
    LEFT JOIN
        dm_group d ON u.id = d.id AND d.entity_type = 'Discipline'
    WHERE
        g.group_type = 'Math'
) AS sub ON gr.id = sub.group_id
GROUP BY
    gr.id
ORDER BY
    gr.id;

    This should return a list of group_ids and the subjects they are linked with, together with the number of users for each subject related to the ‘Math’ group type. Change the table and column names to correspond to your actual database schema.

    Hope it works 🙂

    Login or Signup to reply.
  2. 0

    The question is a mess,
    but here is my guess:

    SELECT id
     , string_agg(name || '(' || dist_users || ')', '; ')
          FILTER (WHERE dist_users > 0) AS "Discipline Counts"
FROM  (
   SELECT g.id, d.name, count(DISTINCT d.id) AS dist_users
   FROM   dm_group           g
   LEFT   JOIN user_to_group ug ON g.id = ug.group_id
   LEFT   JOIN dm_user       u  ON ug.user_id = u.id
   LEFT   JOIN dm_group      d  ON u.id = d.id
                               AND d.entity_type = 'Discipline'
   WHERE  g.group_type = 'Math'
   GROUP  BY g.id, d.name
   ORDER  BY g.id, d.name
   ) sub
GROUP  BY g.id
ORDER  BY g.id;

    Aggregate counts in a subquery. Then aggregate the desired string format at the outer level.

    I added FILTER (WHERE dist_users > 0) to exclude zero counts for missing subjects. Optional.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search