Aggregate by aggregate (ARRAY_AGG)?

postgresql, postgresql-9.1, sql

Solution

I'm not sure if you can aggregate by an array. If you can here is one approach:

select col1, array_agg(col2), ar
from (SELECT a1.column_1 as col1, a2.column_1 as col2,
             ARRAY_AGG(DISTINCT a1.column_2 ORDER BY a1.column_2) as ar
      FROM agg_test a1 JOIN
           agg_test a2
           ON a1.column_2 = a2.column_2 AND a1.column_1 <> a2.column_1
      WHERE a1.column_1 = 1
      GROUP BY a1.column_1, a2.column_1
     ) t
group by col1, ar

The alternative is to use `array_dims` to convert the array values into a string.

Problem

Let's say I have a simple table `agg_test` with 3 columns - `id`, `column_1` and `column_2`. Dataset, for example: ``` id|column_1|column_2 -------------------- 1| 1| 1 2| 1| 2 3| 1| 3 4| 1| 4 5| 2| 1 6| 3| 2 7| 4| 3 8| 4| 4 9| 5| 3 10| 5| 4 ``` A query like this (with self join): ``` SELECT a1.column_1, a2.column_1, ARRAY_AGG(DISTINCT a1.column_2 ORDER BY a1.column_2) FROM agg_test a1 JOIN agg_test a2 ON a1.column_2 = a2.column_2 AND a1.column_1 <> a2.column_1 WHERE a1.column_1 = 1 GROUP BY a1.column_1, a2.column_1 ``` Will produce a result like this: ``` column_1|column_1|array_agg --------------------------- 1| 2| {1} 1| 3| {2} 1| 4| {3,4} 1| 5| {3,4} ``` We can see that for values 4 and 5 from the joined table we have the same result in the last column. So, is it possible to somehow group the results by it, e.g: ``` column_1|column_1|array_agg --------------------------- 1| {2}| {1} 1| {3}| {2} 1| {4,5}| {3,4} ``` Thanks for any answers. If anything isn't clear or can be presented in a better way - tell me in the comments and I'll try to make this question as readable as I can.

Original source