Benjamin Crouzier Benjamin Crouzier - 9 months ago 49
SQL Question

Sort by aggregate value in sql

Let's say I have the following student data:

classroom gender student_id
First Grade M 123
First Grade F 124
First Grade F 125
Second Grade M 126
Third Grade M 127

I want to produce the following result: top 3 biggest classrooms ordered by total number of students with detail for each:

classroom boys_count girls_count total_count
Third Grade 30 30 60
First Grade 20 5 25
Fourth Grade 10 10 20

How can I do that in sql ? If necessary, I can use specific postrges features.

What I tried so far:

SELECT count(*) as total_count
, gender
, classroom
GROUP BY classroom, gender

Then I re-organise results in some scripting language. But this is too slow. I want to have the correct results with one query

Answer Source
select classroom as name,
       sum(case when gender = 'M' then 1 else 0 end) as boys_count,
       sum(case when gender = 'F' then 1 else 0 end) as girls_count,
       count(*) as total_count
from your_table
group by classroom
order by count(*) desc
limit 3