SQL Query, Sum the same entry from ID multiple times
I first ran a query to get the item ID,
SELECT ITEM
FROM `SALES`
WHERE DATE>='201821' AND DATE<='201825' AND CASH_CREDIT='1'
Which returned the results; 1,3,7,4,8,7,5,1
I need to use those ID numbers to add the RETAIL_PRICE of each item together. I've tried the following but it didn't allow for the item ID to be used more than once so it only added the RETAIL_PRICE from 1,3,7,4,8,5 which leaves out the values from the other 7 and 1. I need to add them with those 2 values included.
SELECT SUM(RETAIL_PRICE)
FROM `ITEMS`
WHERE ID IN(1,3,7,4,8,7,5,1)
Thanks in advance.
1 answer

Assuming that
id
is the PRIMARY KEY (or a UNIQUE KEY) inITEMS
table, we can use a JOIN operation, to get all the rows that match, and the add up the retail price from each of the rows.SELECT SUM(i.retail_price) AS tot_retail_price FROM `ITEMS` i JOIN `SALES` s ON s.item = i.id WHERE s.date >= '20180201' AND s.date < '20180206' AND s.cash_credit = '1'
As you observed, this condtion
WHERE ID IN (1,3,7,4,8,7,5,1)
is equivalent to
WHERE ID IN (1,3,4,5,7,8)
is equivalent to
WHERE ID IN (1,1,1,1,1,1,1,3,4,5,7,7,7,7,7,7,7,8)
That query is one pass through the table, evaluating each row in the table. It doesn't matter how many times a value is repeated in the
IN
list. The condition is evaluated, and either a row satisfies the condition or it doesn't. The condition is TRUE or FALSE (or NULL).