Count the Unique Values After Array Join in Presto
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...
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.
Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.
