Getting conditional counts on to the same row in MySQL / SQL
Suppose I have a table, Foo, that looks like this:
ID | Name | Gender | Team
1 | Bob | Male | A
2 | Amy | Female | A
3 | Cat | Female | B
4 | Dave | Male | B
5 | Evan | Male | B
If I wanted to get a list of the number of males and females per team on the same row, how would I do that?
I know I could do SELECT COUNT(Name) as "#", Team, Gender FROM foo GROUP BY Team, Gender
, and that's fine for most purpose.
But that would give me 2 rows per team, like below, and that can be a pain.
# Team Gender
1 | A | Male
1 | A | Female
1 | B | Female
2 | B | Male
How could I开发者_运维知识库 structure the query such that they appear on the same row?
ie,
Team | Males | Females
A | 1 | 1
B | 2 | 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
For the comment
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"
You're close. The answer is
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
The pattern I was looking for was a Self-Join; the syntax and logic is, in my mind, more elegant then the CASE pattern.
Specifically,
SELECT Males.Team, COUNT(Males.id) as "Males", SUM(Males.Points) as "Male Points",
COUNT(Females.id) as "Females", SUM(Females.Points) as "Female Points"
FROM scores as Males
LEFT JOIN scores as Females ON Males.Team=Females.Team AND Females.Gender="Female"
WHERE Males.Gender="Male"
GROUP BY Team
Instead of case statements, the groupings I want in different columns get split into their own copies of the same table. You then join the table of Male players with a Table of Female players on the Team, and then group by the Team.
精彩评论