CODEWITHSUNDEEP
sqlpostgresql
johnsorrentino
johnsorrentino

Reputation: 2731

PostgreSQL - Aggregate column of type integer[] into type integer[]

I have a table that has a column of type integer[]. I want to group by col1, sum col2, and merge the arrays in col3. 

my_table
+-------------------------+
| col1 | col2 | col3      |
| ---- | ---- | --------- |
| qwer | 5    | {1,2,3,4} |
| asdf | 10   | {5,6,7,8} |
| qwer | 2    | {4,9,10}  |
+-------------------------+
SELECT col1, SUM(col2) AS sum, ???(col3) AS merge
FROM my_table
GROUP BY col1

This output would be acceptable:
=> 
+--------------------------------+
| col1 | sum | merge             | 
| ---- | --- | ----------------- |
| qwer | 7   | {1,2,3,4,4,9,10}  | 
| asdf | 10  | {5,6,7,8}         |
+--------------------------------+

The output would be ideal (the elements in the integer array are unique):
=> 
+------------------------------+
| col1 | sum | merge           | 
| ---- | --- | --------------- |
| qwer | 7   | {1,2,3,4,9,10}  | 
| asdf | 10  | {5,6,7,8}       |
+------------------------------+

For ??? I've tried using array_agg as well as converting the array to a string, using string_agg, and then converting back to an array. I couldn't get any of these solutions to work. Am I missing a trivial solution or functionality?

Upvotes: 1

Views: 5110

Answers (1)

Ronaldinho
Ronaldinho

Reputation: 135

The PostgreSQL array_agg function does not accept array parameter. Here's your solution:

The SQL command:

SELECT
    xxx.col1,sum(xxx.col2),array_agg(xxx.one_col) 
FROM (select col1,col2,unnest(col3) as one_col from tmp2) AS xxx 
GROUP BY xxx.col1;

Upvotes: 2

Related Questions