sql - 按sql中的聚合值排序

Question

假设我有以下学生数据:

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

我想产生以下结果:按学生总数排序的前 3 个最大的教室，每个教室都有详细信息:

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

我怎样才能在 sql 中做到这一点？如有必要，我可以使用特定的 postrges 功能。

---

到目前为止我尝试了什么:

SELECT count(*) as total_count
  , gender
  , classroom
ORDER BY 1
GROUP BY classroom, gender
LIMIT 3

然后我用一些脚本语言重新组织结果。但这太慢了。我想通过一次查询获得正确的结果

Answer 1

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