Find sum of price having same IDs in IN clause-CodePudding

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.

CodePudding user response：

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

Fiddle

CodePudding user response：

The in-operator is not suitable for the problem you try to solve. I would solve the problem using prodecures or functions in MySQL. Create a function which takes the IDs as a parameter and return the sum.

The code is PL/SQL for Oracle and not tested but shall give an idea of the solution.

CREATE TYPE num_array as table of number;

CREATE OR REPLACE FUNCTION totalPrice ( ids_in IN num_array ) RETURN number IS 
   total number(10) := 0; 
BEGIN 
   FOR l_id IN ids_in 
   LOOP
     total := total   select price from data where id = l_id;
   END LOOP; 

   RETURN total; 
END; 
/

CodePudding user response：

This also works in PostgreSQL:

SELECT 
    sum(d.price) as total_price
FROM 
    (VALUES (1), (2), (3), (1) ,(2)) AS t(id)
LEFT OUTER JOIN
    data AS d
ON
    t.id = d.id;