curiousBoy curiousBoy - 2 months ago 7
SQL Question

Is it possible to contain duplicated ids in calculation with IN() statement - TSQL?

I am working on an SP that gets the sold product ids as comma separated string as an input.

Restrictions:


  • Cannot add a new input

  • Cannot change the input datatype



I have a function that gets the inputted comma separated string and returns a table including a row for each id.

SELECT * FROM SplitStringIDsIntoTable(@ProductIDs,',')


Then to be able to calculate the cost of good of each sold item (if the item sold 3 times, I need to add its COG 3 times and unfortunately it is out of question to get a new input that tells me the quantity for each sold item), I was doing something like:

SET x = (SELECT SUM(p.COG) FROM Products p WHERE p.ProductId IN
(SELECT * FROM SplitStringIDsIntoTable(@ProductIDs,',')))


But the problem here is, I do not have the quantity as input.
When I use IN() statement, it counts each id ONLY once as expected.

For instance lets say my sold product ids inputted as '11,12,13,11,13'

My sql statement would be:

SET x = (SELECT SUM(p.COG) FROM Products p WHERE p.ProductId IN
('11,12,13,11,13'))


which returns exact same result with :

SET x = (SELECT SUM(p.COG) FROM Products p WHERE p.ProductId IN
('11,12,13'))


which does not give the correct total.
What I am looking for is, is there any statement that takes duplicated values into consideration as well?

I do not want to use While Loop.

PS: I am not sure how to ask this question in title, so question title may look meaningless. Feel free to update it.

Answer

Use JOIN instead of IN:

SELECT @x = SUM(p.COG)
FROM Products p JOIN
     dbo.SplitStringIDsIntoTable(@ProductIDs, ',') ss
     ON p.ProductId = ss.ProductId;