CODEWITHSUNDEEP
postgresql
Spig
Spig

Reputation: 488

How do I aggregate across a column depending on the value of a third column?

I have the following table with three columns:

  id | type | value
-----+------+-------
 foo | bar  | 123
 foo | bar  | 456
 foo | biz  | 789
 foo | biz  | 987
 baz | bar  | 555
 quz | biz  | 777

I'm trying to aggregate value across a given type, returning a result like this:

 id  |      bar      |    biz
-----+---------------+-----------
 foo | {123,456}     | {789,987}
 baz | {555}         | {}
 quz | {}            | {777}

What I've tried so far is self joining the table against itself twice (using LEFT JOIN to account for empty columns) and then doing a DISTINCT to remove duplicates prior to aggregation. Is there a better way?

Current query:

SELECT 
  test.id, 
  ARRAY_REMOVE(ARRAY_AGG(DISTINCT(t1.value)), NULL) AS bar,
  ARRAY_REMOVE(ARRAY_AGG(DISTINCT(t2.value)), NULL) AS biz
FROM test 
LEFT JOIN test t1 ON test.id = t1.id AND t1.type = 'bar'
LEFT JOIN test t2 ON test.id = t2.id AND t2.type = 'biz'
GROUP BY test.id;

Upvotes: 0

Views: 25

Answers (1)

krokodilko
krokodilko

Reputation: 36087

Try:

SELECT id,
       ARRAY_REMOVE( Array_agg( bar ), NULL ) as bbar,
       ARRAY_REMOVE( Array_agg( biz ), NULL ) as bbiz
FROM (
   SELECT id, 
     CASE WHEN type = 'bar' THEN value ELSE NULL END AS bar,
     CASE WHEN type = 'biz' THEN value ELSE NULL END AS biz
   FROM test 
) x
GROUP BY id

Upvotes: 1

Related Questions