3

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.

7

You can use the OVER clause on the COUNT function to get what you need:

CREATE TABLE #inventory(
   InventoryID  INTEGER  NOT NULL PRIMARY KEY 
  ,ItemID       INTEGER  NOT NULL
  ,WarrantyDate DATE  NOT NULL
  ,Status       VARCHAR(9)
);
INSERT INTO #inventory(InventoryID,ItemID,WarrantyDate,Status) VALUES (1,103,'12/22/2010','Available');
INSERT INTO #inventory(InventoryID,ItemID,WarrantyDate,Status) VALUES (2,103,'05/15/2012','Available');
INSERT INTO #inventory(InventoryID,ItemID,WarrantyDate,Status) VALUES (3,103,'02/24/2015',NULL);
INSERT INTO #inventory(InventoryID,ItemID,WarrantyDate,Status) VALUES (4,275,'01/01/2010',NULL);
INSERT INTO #inventory(InventoryID,ItemID,WarrantyDate,Status) VALUES (5,275,'01/01/2011',NULL);

SELECT *, COUNT(ItemID) OVER (PARTITION BY ItemID) AS Quantity
FROM #Inventory

Output:

+-------------+--------+--------------+-----------+----------+
| InventoryID | ItemID | WarrantyDate |  Status   | Quantity |
+-------------+--------+--------------+-----------+----------+
|           1 |    103 | 2010-12-22   | Available |        3 |
|           2 |    103 | 2012-05-15   | Available |        3 |
|           3 |    103 | 2015-02-24   | NULL      |        3 |
|           4 |    275 | 2010-01-01   | NULL      |        2 |
|           5 |    275 | 2011-01-01   | NULL      |        2 |
+-------------+--------+--------------+-----------+----------+
  • Thanks for this., it works as expected now....I tried using the OVER PARTITION BY but I think I had the wrong syntax or columns because it didn't work before. – Robert Feb 26 '16 at 20:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.