mysql - 我想从子查询的结果中计算男性和女性的总数。有没有mysql查询

Question

SELECT name,gender FROM table1 INNER JOIN table2 ON table1.id = table2.form_id WHERE table1.city ="Banglore";

上面的查询显示位于 banglore 的人的姓名和性别。

   Name      Gender
 ___________________
   Riya      Female 
   Ramesh    Male
   Anand     Male
   preety    Female
   Rakesh    Male

现在我想从上面的查询结果中统计记录总数，男性总数和女性总数。(即)输出应该是包含数据的表格(姓名、性别、total_count = 5、total_males = 3、total_females = 2)。

有没有这样的mysql查询？

谢谢

Answer 1

使用聚合

select count(*), count(case when gender='Male' then 1 end) as MaleCount,
count(case when gender='Female' then 1 end) as femalecount
FROM table1 INNER JOIN table2 ON table1.id = table2.form_id 
WHERE table1.city ="Banglore"