CODEWITHSUNDEEP
sqlhaving
Alex
Alex

Reputation: 157

Omit item from Sum SQL

I'm very new to programming and SQL, I can't figure this one out, perhaps I haven't learned the concept yet, but I'm hoping you can help me. Sorry if it's too easy and boring.

/*2.37 Write an SQL statement to display the WarehouseID and the sum of
QuantityOnHand,grouped by WarehouseID. Omit all SKU items that have 3 or more items 
on hand   from the sum, and name the sum TotalItemsOnHandLT3 and display the results 
in descending order of TotalItemsOnHandLT3.*/

SELECT WarehouseID, SUM(QuantityOnHand) AS TotalItemsOnHandLT3
FROM INVENTORY
GROUP BY WarehouseID
HAVING COUNT(WarehouseID) >= 3
ORDER BY TotalItemsOnHandLT3 DESC

Upvotes: 1

Views: 1612

Answers (3)

Abdul Basit
Abdul Basit

Reputation: 11

Its says Omit, they why

HAVING COUNT(WarehouseID) >= 3

and not

HAVING COUNT(WarehouseID) < 3

Upvotes: 0

hashbrown
hashbrown

Reputation: 3516

INVENTORY is the list of products (SKU = Stock Keeping Unit = Individual Product Stored in the warehouse) where every product has a WarehouseID. This warehouseID presumably determines where the product is stored.

By Omit all SKU items, it asks you to only display those products that are stored in minimum 3 places in the warehouse. This can be done with the having clause,

 HAVING COUNT(WarehouseID) >= 3

I do not know the structure and data of your INVENTORY table, but simply put, Consider your data is like this:

SKUID  WareHouseID  QuantityOnHand
1      1            10
1      2            10
2      1            10
1      3            5
2      2            20

In the above case, Product = 1 (SKUID), is stored in 3 different warehouses whereas product 2 is stored in 2 warehouses. Hence,

SKUID  COUNT(WareHouseID)  SUM(QuantityOnHand)
1      3                   25
2      2                   30

In this case, your query will only "Omit" product 1, and not the product 2.

Upvotes: 0

har07
har07

Reputation: 89285

"Omit all SKU items that have 3 or more items on hand from the sum", sounds more like :

FROM INVENTORY WHERE QuantitiyOnHand < 3

rather than :

HAVING COUNT(WarehouseID) >= 3

Upvotes: 1

Related Questions