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

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 2

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.