Count the unique values after array Join in Presto-CodePudding

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

CodePudding user response：

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.