CODEWITHSUNDEEP
sqlsql-servergroup-bysumhaving
dhardy
dhardy

Reputation: 1003

SQL SUM, Group by and Having query

I have a pretty standard query that will get me a list of products that have stock above 0.

This works fine, however I now need to ADD the SUM of all negative numbers for BranchID 31 for the given SKU. 

SELECT * FROM Products WHERE
PrimaryPLU IN 
(select SKU FROM Stock 
WHERE BranchID IN (2,12,1,11,0,96,32,13,14,15) 
AND ApplicationID = @site 
GROUP BY SKU HAVING(SUM(Stock)) > 0))

SO BranchID 31 can have positive and negative numbers, however I just need to get the sum of the negative ones for the SKU, this then needs to be added to the check to see if it's over 0.

Not a clue where to start, was hoping for support from a SQL master!

Thanks in advance!

Dave

Upvotes: 0

Views: 1826

Answers (2)

Rob Paller
Rob Paller

Reputation: 7786

Take the sum of absolute value of the negative numbers (stock) to see if they are non-zero.

Upvotes: 0

András Ottó
András Ottó

Reputation: 7695

Try this:

SELECT * FROM Products WHERE
PrimaryPLU IN 
(select SKU FROM Stock 
WHERE BranchID IN (2,12,1,11,0,96,31,32,13,14,15) 
AND ApplicationID = @site 
GROUP BY SKU HAVING(SUM(CASE WHEN Stock < 0 AND BranchID = 31 
              THEN Stock ELSE 0 END)) > 0))

Upvotes: 1

Related Questions