count columns group by

Question

I hava the sql as below:

select a.dept, a.name
  from students a
 group by dept, name
 order by dept, name

And get the result:

dept   name
-----+---------
CS   | Aarthi
CS   | Hansan
EE   | S.F
EE   | Nikke2

I want to summary the num of students for each dept as below:

dept   name        count
-----+-----------+------  
CS   | Aarthi    |  2
CS   | Hansan    |  2
EE   | S.F       |  2
EE   | Nikke2    |  2
Math | Joel      |  1

How shall I to write the sql?

Answer 1

select a.dept, a.name,
       (SELECT count(*)
          FROM students
         WHERE dept = a.dept)
  from students a
 group by dept, name
 order by dept, name

This is a somewhat questionable query, since you get duplicate copies of the department counts. It would be cleaner to fetch the student list and the department counts as separate results. Of course, there may be pragmatic reasons to go the other way, so this isn't an absolute rule.

Answer 2

This should do it (I haven't got any environment to test on at the min)

select a.dept, a.name, count(a.*) as NumOfStudents
from students a
group by dept, name order by dept, name

HTH

Answer 3

SELECT dept, name, COUNT(name) as CT from students
group by dept, name
order by dept, name

Answer 4

Although it appears you are not showing all the tables, I can only assume there is another table of actual enrollment per student

select a.Dept, count(*) as TotalStudents
  from students a
  group by a.Dept

If you want the total count of each department associated with every student (which doesn't make sense), you'll probably have to do it like...

select a.Dept, a.Name, b.TotalStudents
    from students a,
        ( select Dept, count(*) TotalStudents
             from students
             group by Dept ) b
    where a.Dept = b.Dept

My interpretation of your "Name" column is the student's name and not that of the actual instructor of the class hence my sub-select / join. Otherwise, like others, just using the COUNT(*) as a third column was all you needed.

Answer 5

Or Otherwise write simply

select dept, name, count(name) as nostud from students group by dept, name order by dept, name

Answer 6

This will give the results requested above

select a.dept, a.name, cnt
from student a
join (
select dept, count(1) as cnt
from student
group by dept
) b on b.dept = a.dept