How to count number of members by combining two tables?

[Suzukaze]

I have 3 tables; Group, MemberOfGroup and Graduates. GroupID is a primary key for Group and MatricNo is a primary key for Graduates. Both GroupID and MatricNo are composite key for MemberOfGroup. I want to show the group lists according to member size. Below is my sql.

Select g.GroupName, count(m.MatricNo) AS "Member Size"
from Group g, MemberOfGroup m
where g.GroupID=m.GroupID;

But I am getting error saying this was wrong. I have tried in many ways, including "group by" but still getting error. Please guide me what was wrong.

Thanks ahead.

[darkagn]

Hi Suzukaze and welcome to DaniWeb :)

You need to join the tables, the syntax is like this:

SELECT g.GroupName, COUNT(m.MatricNo) AS "Member Size"
FROM Group G
-- join to the MemberOfGroup table
INNER JOIN MemberOfGroup M
-- describe how the tables are related
ON m.GroupID = g.GroupID
-- COUNT function needs a GROUP BY clause to describe how to count
GROUP BY GroupName

You may need to experiment with your join, a left or right outer join might be required if you have nullable fields in either table. You can read up on JOINS here.

EDIT: Actually another problem might be that you have a table called "GROUP" which is a reserved SQL word. MSSQL might be thinking that you are attempting to group by without specifying the table name in the FROM clause. I'm not sure whether MSSQL cares or not about this, but it might be less confusing either way if you are able to change the name of the table.

[BitBlt]

My $.02:
Yes, SQL Server does care about using reserved words...sometimes. ;-)

However, you can get by potential problems of this sort by enclosing the offending reserved word in square brackets, so [GROUP] would be acceptable.

However, to further hint that you're using the reserved word as a database object, include the schema name (or owner, if you're using SQL2000 or 2005) i.e. dbo.[GROUP]. That will tell anyone reading your code that this is not being used as a reserved word.

To darkagn, thank you thank you thank you for using "JOIN" syntax instead of column correlation syntax...it makes the table relationships SO much clearer, and makes left/right/cross joins easier to work with.