Find sum of price having same IDs in IN clause

Question

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.

https://stackoverflow.com/questions/2259160/force-mysql-to-return-duplicates-from-where-in-clause-without-using-join-union — Ergest Basha, Feb 03 '22 at 10:37

score 0 · Accepted Answer · answered Feb 03 '22 at 10:34

0

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

answered Feb 03 '22 at 10:34

Zakaria

4,715
2
5
31

If I have thousands of similar IDs then my query will be too long. So is there any other way to get it. – John Doe Feb 03 '22 at 10:39
Where are you getting these IDs from? – Zakaria Feb 03 '22 at 10:40
from another query like this SELECT SUM(price) FROM data WHERE id IN (SELECT id from abc etc) – John Doe Feb 03 '22 at 10:42
@JohnDoe you can just put your (SELECT id from abc etc) inside the CTE: `with u as(SELECT id from abc etc) select sum(price) from data natural join u` – Zakaria Feb 03 '22 at 10:45
will this work ? – John Doe Feb 03 '22 at 10:46
with u as(1,2,3,1,2) select sum(price) from data natural join u – John Doe Feb 03 '22 at 10:47
this is not working – John Doe Feb 03 '22 at 10:47
@JohnDoe you need to put the query that returns your ids inside the CTE, not the ids themselves! – Zakaria Feb 03 '22 at 10:50

score 0 · Answer 2 · answered Feb 03 '22 at 11:01

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; 
/

score 0 · Answer 3 · answered Feb 03 '22 at 15:29

0

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;

answered Feb 03 '22 at 15:29

MAM

91
1
4

Find sum of price having same IDs in IN clause

3 Answers3