CODEWITHSUNDEEP
sql
e.b.white
e.b.white

Reputation: 793

count columns group by

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?

Upvotes: 13

Views: 30782

Answers (6)

souLTower
souLTower

Reputation: 181

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

Upvotes: 1

Marcelo Cantos
Marcelo Cantos

Reputation: 186118

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.

Upvotes: 6

BlackICE
BlackICE

Reputation: 8926

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

Upvotes: 0

Karthik
Karthik

Reputation: 3301

Or Otherwise write simply

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

Upvotes: 0

DRapp
DRapp

Reputation: 48179

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.

Upvotes: 20

OneSHOT
OneSHOT

Reputation: 6953

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

Upvotes: 0

Related Questions