CODEWITHSUNDEEP
mysqlsqlgroup-by
Yahel
Yahel

Reputation: 37315

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

Upvotes: 3

Views: 1931

Answers (2)

Yahel
Yahel

Reputation: 37315

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.

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

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

Upvotes: 5

Related Questions