Find sum of price having same IDs in IN clause

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


Leave a Reply