Select grouping columns and Non-Group columns

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

General SQL Server Forums

New to SQL Server Programming

Select grouping columns and Non-Group columns

Author

Topic

sureshsmanian
Starting Member

31 Posts

Posted - 2011-06-28 : 13:49:55

Hi everyone,
I have two tables

Student (RegNo - PK, Name)
Subject (ID, StudentId(refers the Student table), SubjectName)

Student
-------
RegNo Name
----- ----
10 Amit
20 Pankaj
30 Kumar

Subject
-------
ID StudentId SubjectName
---------------------------
1 10 English
2 10 Maths
3 10 Physics
4 20 Economics
5 30 Physics
6 30 Biology

I want the output in the following format

StudentId Name Count(Subjects)
--------------------------------
10 Amit 3
20 Pankaj 1
30 Kumar 2

Thanks for your help.

Regards
SSM

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-06-28 : 13:54:50

First an Inner Join at the Student ID = RegNo Then a GRoup By statement. Use this as an aid: http://www.w3schools.com/sql/sql_groupby.asp

Dasman

==========================
Pain is Weakness Leaving the Body.

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-06-28 : 13:59:35

This should work:
Select RegNo, Name FROM Student
Inner Join Subject
ON Subject.StudentID = Student.RegNo
Group By Name

==========================
Pain is Weakness Leaving the Body.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-28 : 14:04:32

quote:
Originally posted by Dasman

This should work:
Select RegNo as StudentId, Name,count(*) as [Count(Subjects)] FROM Student
Inner Join Subject
ON Subject.StudentID = Student.RegNo
Group By Name

==========================
Pain is Weakness Leaving the Body.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources