joining result of SELECT COUNT to another SELECT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

joining result of SELECT COUNT to another SELECT

Author

Topic

gnobber
Starting Member

11 Posts

Posted - 2008-02-05 : 15:28:35

hi everyone,

Ive got a bit of a problem. I have a table InventoryItem and another table InventoryItemPieces. I have a routine that selects all InventoryItem but what I want is to select all InventoryItem and select the count of each piece of InventoryItem (in InventoryItemPieces) and then relate that count to the corresponding InventoryItem to make a single result set. The columns would be like InventoryItem.ID, InventoryItem.Name, COUNT(InventoryItemPiece). Is there a way to do this in a single query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-05 : 22:52:59

SELECT i.ID,i.[Name],p.PieceCount
FROM InventoryItem i
INNER JOIN (SELECT InventoryItemID,COUNT(InventoryItemPiece) AS PieceCount
FROM InventoryItemPieces
GROUP BY InventoryItemID)p
ON p.InventoryItemID=i.ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources