13

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?

Marcelo Cantos
  • 181,030
  • 38
  • 327
  • 365
e.b.white
  • 783
  • 3
  • 7
  • 17

6 Answers6

20

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.

DRapp
  • 47,638
  • 12
  • 72
  • 142
6
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.

Marcelo Cantos
  • 181,030
  • 38
  • 327
  • 365
  • edited version is correct, and I think is actually cleaner SQL than the other two that are correct. – BlackICE Apr 23 '10 at 12:58
1
SELECT dept, name, COUNT(name) as CT from students
group by dept, name
order by dept, name
Marcelo Cantos
  • 181,030
  • 38
  • 327
  • 365
souLTower
  • 181
  • 1
  • 3
0

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

OneSHOT
  • 6,903
  • 2
  • 23
  • 24
0

Or Otherwise write simply

select dept, name, count(name) as nostud from students group by dept, name order by dept, name
Karthik
  • 3,221
  • 5
  • 28
  • 38
0

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
BlackICE
  • 8,816
  • 3
  • 53
  • 91