I have an Items table and an Inventory table. An Item can belong to multiple Inventory records. I'm trying to return a list of ALL Inventory records but with their quantity in a new column. For instance:
Items
ItemID ItemDescription
103 Headphones
115 Speakers
230 Wireless Adapter
275 20' Network Cable
Inventory
InventoryID ItemID WarrantyDate Status
1 103 12/22/2010 Available
2 103 05/15/2012 Available
3 103 02/24/2015
4 275 01/01/2010
5 275 01/01/2011
If I try to COUNT
the ItemID and GROUP BY
ItemID, like so:
SELECT ItemID, COUNT(ItemID) AS Quantity
FROM Inventory
GROUP BY ItemID
I get:
ItemID Quantity
103 3
275 2
But what I really want is this:
InventoryID ItemID WarrantyDate Status Quantity
1 103 12/22/2010 Available 3
2 103 05/15/2012 Available 3
3 103 02/24/2015 3
4 275 01/01/2010 2
5 275 01/01/2011 2
Any suggestions/ideas are appreciated.
Best Answer
You can use the
OVER
clause on theCOUNT
function to get what you need:Output: