SQL HAVING SUM GROUP BY

Question

Using SQL Server 2005. I am building an inventory/purchasing program and I’m at the point where I need the user to “check out” equipment. When he selects a product, I need to query which stock locations have the available Qty, and tell the user which location to walk to/ retrieve product.

Here is a query for a particular [StockLocation_Products].ProductID, with a particular assigned [ProductUsages].ProductUsageID.

SELECT 
  PROD.ProductID,
  PROD.ProductName,
  SL.Room,
  SL.StockSpace,
  SLPPU.ResvQty,
  PRDUSG.ProductUsage
FROM [StockLocations] SL 
INNER JOIN [StockLocation_Products] SLP ON SL.StockLocationID = SLP.StockLocationID 
INNER JOIN [StockLocation_Product_ProductUsages] SLPPU ON SLP.StockLocationID = SLPPU.StockLocationID AND SLP.ProductID = SLPPU.ProductID 
INNER JOIN [ProductUsages] PUSG ON SLPPU.ProductUsageID = PRDUSG.ProductUsageID 
INNER JOIN [Products] PROD ON SLPPU.ProductID = PROD.ProductID
WHERE SLP.ProductID = 4 AND PRDUSG.ProductUsageID = 1

This query returns:

ProductID ProductName           Room    StockSpace  ResvQty ProductUsage
------------------------------------------------------------------------------------------------------------------------
4   Addonics Pocket DVD+/-R/RW  B700    5-D         12      MC Pool
4   Addonics Pocket DVD+/-R/RW  B700    6-B         10      MC Pool
4   Addonics Pocket DVD+/-R/RW  B700    6-C         21      MC Pool
4   Addonics Pocket DVD+/-R/RW  B700    6-D         20      MC Pool

I thought maybe I could use an additional HAVING clause to make this query return which combination of StockSpace(s) you’d need to visit to satisfy a request for some Qty. E.g. User needs to pull 30 of Product (ID =4).

But I don’t really understand how to use GROUP BY with HAVING SUM(), to achieve what I want.

I tried various things in my group by / having clause, but I just don’t get any results.

GROUP BY PROD.ProductID,PROD.ProductName,SL.Room,SL.StockSpace,SLPPU.ResvQty,PUSG.ProductUsage
HAVING SUM(ResvQty) >= 30;

I want results that show (at least one) combination of StockSpaces which sums up to 30, so I can tell the user “you can get 21 units from space ‘6-C’, and 9 units from ‘6-B’. There may be multiple combinations of rows that could sum() >= 30, but I need at least how to find one combination that does! Help!

Answer 1

You can have an inner select, such as:

SELECT count_of_foo, count(bar), baz
FROM (SELECT count(foo) as count_of_foo, bar, baz, other1, other2 FROM complex_query WHERE foo = bar HAVING count(foo) > 1) inner_query
GROUP BY count_of_foo, baz.

This will give you the ability to add more group by after the HAVING clause.