Add a total quantity column for groups of rows

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.

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 |
+-------------+--------+--------------+-----------+----------+

StackExchange Database Administrators Q#130591, answer score: 7