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

Can Postgres Query aggregate & individually list data?

So this is going to be a bit of an odd question but I am absolutely stuck on how to do this and it’s blocking me from being able to finish a task I am working on.

I have the following DB structure (I know, its not pretty):

users

|   id   | company  |
| -------| -------- |
| user1  | company1 |
| user2  | company2 |

------

users_companies

| user_id| company  |
| -------| -------- |
| user1  | company3 |
| user1  | company4 |
| user2  | company5 |
| user2  | company6 |

I need to write a query that will return the following result set

| user1  | company1 |
| user1  | company3 |
| user1  | company4 |
| user1  | company1,company3,company4 |
| user2  | company2 |
| user2  | company5 |
| user2  | company6 |
| user2  | company2,company5,company6 |

As you can see for each user it will individually list out each company and also create an aggregate row of all their companies they are assigned to. I am completely lost on how to get this result set and any help in the right direction would be super helpful. Thanks!

Tags:

3

Answers


  1. 0

    I think you’ll be able to get this with a little hint:

    Make a query that gives you the aggregated data and then UNION ALL it with the query that gives you the non-aggregated data.

    Login or Signup to reply.
  2. 0

    You can accomplish this by approaching it as two separate queries which you can union using a separate union of the data provided by a CTE; each query is a derived table with a defined sequence which you can use for ordering:

    with u as (
  select * 
  from users
  union all
  select * 
  from users_companies
)
select id, company
from (
  select id, Company, 1 seq
  from u
  union all
  select id, string_agg(company, ','), 2 seq
  from u
  group by Id, seq
)t
order by Id, seq, company;

    See a Fiddle demo

    Login or Signup to reply.
  3. 0

    The following is an alternative using GROUPING SETS:

    WITH uc AS (
    SELECT users.id AS user_id, users.company
      FROM users
    UNION
    SELECT user_companies.user_id, user_companies.company
      FROM user_companies
)
SELECT uc.user_id,
       STRING_AGG(uc.company, ', ' ORDER BY uc.company) AS company
  FROM uc
 GROUP BY GROUPING SETS (uc.user_id, (uc.user_id, uc.company))
 ORDER BY uc.user_id;

    Please note that UNION is used instead of UNION ALL to protect against duplicate rows caused by user/company pairs occurring in both tables.

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

Back To Top
Search