trying to run a script in athena where I can pull back customers who have made purchases of two specified values (14.45 and 17.45). Thought I would make a column for each value appearing and filter out for both columns >0 when downloaded onto excel but my code isn't working, any help.
select order_customer_id,
sum(invoice_total_price= cast('14.45' as decimal(20,2))) > 0,
sum(invoice_total_price = cast('17.45' as decimal(20,2))) > 0
from orders
where year_month_day between '2022-01-10' and '2022-03-14'
group by order_customer_id
Get this error when I run it
Unexpected parameters (boolean) for function sum. Expected: sum(double) , sum(real) , sum(bigint) , sum(interval day to second) , sum(interval year to month) , sum(decimal(p,s))
I done the cast within the two sum columns as the invoice_total_price is stored as decimal
You can use count_if, also potentially cast from string is not needed:
select order_customer_id,
count_if(invoice_total_price = 14.45) > 0 has_14,
count_if(invoice_total_price = 17.45) > 0 has_17
from orders
where year_month_day between '2022-01-10' and '2022-03-14'
group by order_customer_id
Which will give you a table with 3 corresponding columns. If you don't need them in the output you can consider moving checks into HAVING clause:
select order_customer_id
from orders
where year_month_day between '2022-01-10' and '2022-03-14'
group by order_customer_id
having count_if(invoice_total_price = 14.45) > 0
and count_if(invoice_total_price = 17.45) > 0