I have 3 columns as below
| col1 | col2 | price |
|---|---|---|
| abc | 12345 | 10 |
| abc | 12345 | 10 |
| bcd | 45689 | 15 |
| abc | 78945 | 20 |
| bcd | 54782 | 13 |
| def | 12345 | 10 |
| def | 12345 | 10 |
I looking to get result as below.
| col1 | col2 | count | Amount |
|---|---|---|---|
| abc | 12345,78945 | 2 | 30 |
| bcd | 45689,54782 | 2 | 28 |
| def | 12345 | 1 | 10 |
1 Answer
GROUP BY into array_agg with distinct and cardinality to count the length should produce required result:
WITH dataset (col1, col2) AS ( VALUES ('abc', 12345), ('abc', 12345), ('bcd', 45689), ('abc', 78945), ('bcd', 54782), ('def', 12345), ('def', 12345) ) --query select col1, array_agg(distinct col2) col2, cardinality(array_agg(distinct col2)) count from dataset group by col1 order by col1 -- for output ordering Output:
| col1 | col2 | count |
|---|---|---|
| abc | [12345, 78945] | 2 |
| bcd | [45689, 54782] | 2 |
| def | [12345] | 1 |
If you need different formatting for col2 - use array_join.
