I have a small query related to calculating the total price having same IDs as twice or multiple times in the IN clause.
Let me explain: I have the following table named data
id | price |
===============
1 | 100 |
2 | 150 |
3 | 200 |
I am executing the following query:
SELECT SUM(price) FROM data WHERE id IN (1,2,3,1,2)
It return me 450
But I want to get 700
Please someone help.
>Solution :
What you can do is a a join between a set of the ids you want and your table, then get the sum:
with u as
(select 1 as id
union all select 2
union all select 3
union all select 1
union all select 2)
select sum(price)
from data natural join u