0

I'm trying to sum the total of items in a cart by ID, so i was trying simply to SUM the values by passing IDs in the WHERE clause.

But if i'm using the WHERE IN to add the IDs the SUM is going to sum only different IDs so if i have in WHERE ID 104 twice it will sum it only once.

My query looks like this:

SELECT SUM(IMPORTO) AS TOTAL
FROM (
    SELECT SUM(PRE_PRP) AS IMPORTO
    FROM vo_plurep
    WHERE ID_PLUREP IN (12782 , 12800, 12804, 12810, 12802, 12798, 12784, 12798, 12802) 

    UNION ALL 
 
    SELECT SUM(PRE_VAR) AS IMPORTO
    FROM vo_varianti
    WHERE ID_VARIANTI IN (104 , 104, 142)
) AS TOT

Is there a way to force MySql to sum even the same values in where clause?

  • 1
    No, WHERE IN does not allow this, You must convert your values list into the rowset (with duplicated values) and join it to source table. – Akina Jun 30 at 10:54
1

The IN predicate will either evaluate to TRUE or FALSE (or NULL) exactly once for each row, so you won't get any multiplier effect from that. You can use a Common Table Expression (CTE, only supported in 8+) or a temporary table

CREATE TABLE vo_plurep
( ID_PLUREP int not null
, PRE_PRP int not null
);

insert into vo_plurep (id_plurep, pre_prp) values (1,1),(2,2);

CREATE TEMPORARY TABLE T (id_plurep int not null);

insert into T (id_plurep) values (1),(1);

SELECT SUM(PRE_PRP) AS IMPORTO
FROM vo_plurep
JOIN T
    ON T.id_plurep = vo_plurep.id_plurep


IMPORTO
2

Fiddle

| improve this answer | |
1

Convert

SELECT SUM(PRE_VAR) AS IMPORTO
FROM vo_varianti
WHERE ID_VARIANTI IN (104 , 104, 142)

to, for example,

SELECT SUM(PRE_VAR) AS IMPORTO
FROM vo_varianti
JOIN (SELECT 104 ID_VARIANTI UNION ALL 
      SELECT 104 UNION ALL
      SELECT 102) criteria USING (ID_VARIANTI)

or use any other technique for to create a rowset (for example, transfer criteria as JSON or CSV and parse it to rowset with JSON_TABLE() function).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.