Joining the sum of a COUNT() - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Joining the sum of a COUNT()

Author

Topic

masboat
Starting Member

2 Posts

Posted - 2007-09-03 : 00:11:26

I have two tables, I am trying to join the result of a COUNT() for each row from another table.

I have these two tables:

tblProducts:
ItemID | Info | InventoryCount
1      |      | 
2      |      | 
3      |      | 

tblInventory:
ItemID | DateAdded
1      | 
1      | 
3      |

This is the desired output:

ItemID | Info | InventoryCount
1      |      | 2
2      |      | 0
3      |      | 1

This pseudo-SQL is roughly what I am attempting to do:

SELECT *
FROM tblProducts
INNER JOIN (
   SELECT COUNT(*)
   FROM tblInventory
   WHERE tblInventory.ItemID = tblProducts.ItemID)

I'm using SQL Server 2000, but would prefer to use ANSI SQL. Any suggestions are welcome.

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-09-03 : 01:08:50

SELECT A.ItemID, A.Info, InventoryCount = (SELECT COUNT(*) FROM tblInventory Z WHERE Z.ItemID = A.ItemID)
FROM tblProducts A
ORDER BY A.ItemID

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2007-09-03 : 02:20:16

Use Group BY
SELECT A.ItemID, A.Info, count(B.ItemId) as InventoryCount
FROM tblProducts A
INNER JOIN tblInventory B ON A.ItemID = B.ItemID
Group BY A.ItemID, A.Info
ORDER BY A.ItemID

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-09-03 : 02:59:57

quote:
Originally posted by shallu1_gupta

Use Group BY
SELECT A.ItemID, A.Info, count(B.ItemId) as InventoryCount
FROM tblProducts A
INNER JOIN tblInventory B ON A.ItemID = B.ItemID
Group BY A.ItemID, A.Info
ORDER BY A.ItemID

This will not return InventoryCount = 0.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-03 : 04:05:41

[code]SELECT p.*,
ISNULL(d.Items, 0) AS Items
FROM tblProducts AS p
LEFT JOIN (
SELECT ItemID,
COUNT(*) AS Items
FROM tblInventory
GROUP BY ItemID
) AS d ON d.ItemID = p.ItemID[/code]

E 12°55'05.25"
N 56°04'39.16"

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-09-03 : 04:09:58

Thank you, Peso.

masboat
Starting Member

2 Posts

Posted - 2007-09-03 : 19:45:50

Thanks a lot for the responses. The solutions from Koji and Peso both work great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources