mysql - 在 MySQL/SQL 中对同一行进行条件计数

Question

假设我有一张 table ，Foo，它看起来像这样:

ID  | Name  | Gender  | Team
1   | Bob   | Male    | A
2   | Amy   | Female  | A
3   | Cat   | Female  | B
4   | Dave  | Male    | B
5   | Evan  | Male    | B

如果我想获取在同一行的每个团队的男性和女性人数列表，我该怎么做？

我知道我可以SELECT COUNT(Name) as "#", Team, Gender FROM foo GROUP BY Team, Gender，这对大多数用途来说都很好。

但这会给我每个团队 2 行，如下所示，这可能会很痛苦。

#  Team Gender
1 | A | Male
1 | A | Female
1 | B | Female
2 | B | Male

如何构造查询以使它们出现在同一行？

即，

Team | Males | Females
A    |   1   | 1
B    |   2   | 1

Answer 1

select team,
 SUM(case when gender='Male' then 1 else 0 end) Male,
 SUM(case when gender='Female' then 1 else 0 end) Female
from tbl
group by team

评论

Imagine now that each row has an arbitrary numeric score associated with it, in a Score column. How would I have 'Male points' and 'Female points? Would that be SUM(case when gender="male" then select points else 0 end) "Male Points"

你很接近。答案是

select team,
 SUM(case when gender='Male' then 1 else 0 end) Male,
 SUM(case when gender='Male' then points else 0 end) `Male Points`,
 SUM(case when gender='Female' then 1 else 0 end) Female,
 SUM(case when gender='Female' then points else 0 end) `Female Points`
from tbl
group by team