CODEWITHSUNDEEP
sqlsql-servert-sql
Frenz
Frenz

Reputation: 747

SQL to get Count of Members in each group

I have a Group table with the columns GroupId and GroupName. Also there is another table called Group_Student which keeps track of which group a student belongs. It contains the columns GroupId (foreign key to the Group table) and StudentId.

I would like to know on how I can write a SQL query which lists the GroupName, GroupId and the number of students in each group.

Example, if the Group_Student table contains the following entries

GroupId   StudentId  
-------------------
  1          2 
  1          3 
  2          4

Then the SQL query should produce the following output

GroupName    MemberCount
------------------------
   ABC           1   
   DEF           2

Kindly let me know how I can write the SQL for this. I'm using SQL Server 2005. Thanks in advance.

Upvotes: 1

Views: 7645

Answers (3)

adey_888
adey_888

Reputation: 316

SELECT GroupName, Group.GroupID, COUNT(StudentId) AS MemberCount 
FROM Group 
INNER JOIN Group_Student ON Group.GroupID = Group_Student.GroupID 
GROUP BY Group.GroupID

Upvotes: 1

JanR
JanR

Reputation: 6130

Try the query below:

It will join the two tables, and then add a count to it. The GROUP BY clause ensures that the values for the count are grouped by GroupName and GroupId

SELECT GroupName, a.GroupId, count(StudentId) "MemberCount"
FROM Group a
JOIN Group_student b on a.GroupId= b.GroupId
GROUP BY GroupName, a.GroupId

Upvotes: 0

Avitus
Avitus

Reputation: 15968

Here is a sql fiddle of this working

http://sqlfiddle.com/#!3/0f8a5/2/0

select 
   groupname, [group].groupid, count(*) as 'MemberCount'
from [group]
inner join group_student on [group].groupid = group_student.groupid
group by groupname, [group].groupid

Upvotes: 1

Related Questions