Getting conditional counts on to the same row in MySQL / SQL -

suppose have table, foo, looks this:

id  | name  | gender  | team 1   | bob   | male    | 2   | amy   | female  | 3   | cat   | female  | b 4   | dave  | male    | b 5   | evan  | male    | b 

if wanted list of number of males , females per team on same row, how that?

i know select count(name) "#", team, gender foo group team, gender, , that's fine purpose.

but give me 2 rows per team, below, , can pain.

#  team gender 1 | | male 1 | | female 1 | b | female 2 | b | male 

how structure query such appear on same row?

ie,

team | males | females    |   1   | 1 b    |   2   | 1 

select team,  sum(case when gender='male' 1 else 0 end) male,  sum(case when gender='female' 1 else 0 end) female tbl group team 

for comment

imagine each row has arbitrary numeric score associated it, in score column. how have 'male points' , 'female points? sum(case when gender="male" select points else 0 end) "male points"

you're close. answer is

select team,  sum(case when gender='male' 1 else 0 end) male,  sum(case when gender='male' points else 0 end) `male points`,  sum(case when gender='female' 1 else 0 end) female,  sum(case when gender='female' points else 0 end) `female points` tbl group team