Marco Aurélio Deleu Marco Aurélio Deleu - 5 months ago 12
MySQL Question

Allow repeat record with IN clause

This kind of seem a simple task, but I guess I don't know the proper words to find it on the internet.

what I want is simply this:

SELECT SUM(price) FROM products WHERE id IN (5, 10, 10, 10, 13, 15, 18, 18);


This would return me basically the result of:

price_of_5 + price_of_10 + price_of_10 + price_of_10 +
price_of_13 + price_of_15 + price_of_18 + price_of_18


That is, the concept is to SELECT exactly as many rows as specified at IN clause, even if a value is specified more than once.

Edit



Here is a simple testing environment (or a fiddle)

1- Create the table

CREATE TABLE products (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) NOT NULL,
`price` float NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


2- Feed the table

INSERT INTO products VALUES (1, 'Apple', 5), (2, 'Orange', 6), (3, 'Grape', 10), (4, 'Watermelon', 20), (5, 'Cashew', 7);


3- Example of what I would like:

SELECT SUM(price) FROM products WHERE id IN (1, 2, 2, 4);


Results in: 31 (5 + 6 + 20)

Expected: 37 (5 + 6 + 6 + 20)

Answer

Here is my approach:

SELECT SUM(p.price) 
FROM (
        SELECT 5 id
        UNION ALL 
        SELECT 10 
        UNION ALL
        SELECT 10 
        UNION ALL
        SELECT 10 
        UNION ALL
        SELECT 13 
        UNION ALL
        SELECT 15 
        UNION ALL
        SELECT 18 
        UNION ALL
        SELECT 18 
    ) as t

INNER JOIN products p
ON t.id = p.id;