select count on joined table - 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

Transact-SQL (2000)

select count on joined table

Author

Topic

lordinateur
Starting Member

2 Posts

Posted - 2003-11-24 : 14:13:31

I'm trying to select the count of a record in a mapping table for each record selected on a main table.

I've a few things but none seem to do the trick. Essentially, I'm trying to do this:

Select g.GroupID, g.GroupName, count(gm.UserID) FROM _Group g
JOIN _GroupMembers gm ON g.GroupID = gm.GroupID GROUP BY...

the problem with this is that it will only return records for Groups that HAVE members and so existin the _GroupMembers table. I want to get the count of members in each group (0 if empty).

I thought of a possible inner select statement but I need to do that for each record in the _Group table. Does anyone know how to do this correctly?

Thanks,

L

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-24 : 14:32:51

Select g.GroupID, g.GroupName, sum(case when gm.UserID is null then 0 else 1 end
FROM _Group g
left outer JOIN
_GroupMembers gm
ON g.GroupID = gm.GroupID
GROUP BY...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Granick
Starting Member

46 Posts

Posted - 2003-11-24 : 14:34:26

Likely your problem is simple as using an outer join, as follows:

Select
g.GroupID,
g.GroupName,
count(gm.UserID)
FROM
_Group g
LEFT OUTER JOIN _GroupMembers gm ON g.GroupID = gm.GroupID GROUP BY...

This should give you rows for EVERY item in "g" that matches your criteria, with zeros where there are no recors in the count.

Give it a try and see if that make a difference.

Hope that helps,
Shannon

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-24 : 14:34:44

Why do the join then?


   SELECT GroupID, GroupName, COUNT(*)) 
     FROM _Group
 GROUP BY GroupID, GroupName

Do you also want to know if exists there or not?


   SELECT GroupID, GroupName, COUNT(*),
          CASE WHEN GrupName Is Null THEN 'N' ELSE 'Y' END AS Exists
     FROM _Group g 
LEFT JOIN _GroupMembers gm 
       ON g.GroupID = gm.GroupID 
 GROUP BY GroupID, GroupName

Brett

8-)

Granick
Starting Member

46 Posts

Posted - 2003-11-24 : 14:35:16

Doh, that is what I get for letting work distract me as I am typing a response. :)

Shannon

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-24 : 15:01:23

I would actually do it this way:


SELECT 
  g.GroupID, g.GroupName, ISNULL(MbrCount,0) as MemberCount
FROM 
  _Groups G
LEFT OUTER JOIN
  (SELECT groupID, count(*) as MbrCount 
   FROM _GroupMembers 
   GROUP BY GroupID) GM
ON
  G.GroupID = GM.GroupID

- Jeff

lordinateur
Starting Member

2 Posts

Posted - 2003-11-24 : 15:36:15

You guys are awesome. I went with Jeff's idea. Works great. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources