sum unique values after array join in PRESTO

Question

I have 3 columns as below

col1	col2	price
abc	12345	10
abc	12345	15
bcd	45689	15
abc	78945	20
bcd	54782	13
def	12345	10
def	12345	10

I looking to get result as below. Sum the price only once for col2 value. For ex. 12345 has $10 & $15. So I want to consider only once. anyting is fine (10 or 15) but only once in the sum

col1	col2	count	Amount
abc	12345,78945	2	30
bcd	45689,54782	2	28
def	12345	1	10

Answer 1

I don't have access to presto to test so I have tested in mySQL and converted.
I have used a sub-query to get the average price for each col1 - col2 pair using avg(). You could use min() or max() if preferred or if avg() is not recognised in presto.

select
col1,
array_join(array_agg(col2), ','),
sum(av_price)
from 
  (  select 
     col1,col2, avg(price) av_price
     from p
     group by col1,col2
  ) as subQuery
group by col1
order by col1;

Here is the version mySQL, the result and the dbFiddle link

select
col1,
group_concat(col2),
sum(av_price)
from 
  (  select 
     col1,col2, avg(price) av_price
     from p
     group by col1,col2
  ) as subQuery
group by col1
order by col1

col1 | group_concat(col2) | sum(av_price)
:--- | :----------------- | ------------:
abc  | 12345,78945        |       32.5000
bcd  | 45689,54782        |       28.0000
def  | 12345              |       10.0000

db<>fiddle here