Benjamin Crouzier Benjamin Crouzier - 27 days ago 7
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
ORDER BY 1
GROUP BY classroom, gender
LIMIT 3


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
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