Can I have multiple COUNT() on same table?

**Robo** · Jun 14, 2003, 19:47

Using MySQL, I have an athlete table, with ath_no and team_no

A team table that has the key team_no

And a table call extras, that has the ath_no, a column call tshirt_size, the tshirt column can either be NULL, S, M, or L. There's another column call ticket (to see if the athlete has a ticket or not), and it can be either 0 or 1.

A team has many athletes, and each athlete belongs to one team, each athlete has a tshirt_size and a 0 or 1 in the ticket column in the extras table.

Is it possible that for each team, to count the number of shirts for each size and the number of tickets?

eg. a query that will generate something like:

Code:

TeamName    S    M    L    Tickets
----------------------------------
Team 1      5    0    2         17
Team 2      4    5    0          8

It won't be hard to use multiple queries with a bit of help from PHP, just wondering if there's a pure SQL way to do this

**Robo** · Jun 14, 2003, 22:22

I've found the solution, after coming across one of the question that r937 answered over at Ask the Experts:

http://searchdatabase.techtarget.com...285649,00.html

Here's the query I ended up with, the names are slightly different to the names I mentioned in the first post, but you get the idea:

Code:

select 
t.team_name, 
sum(case when x.tshirt = 'S' then 1 else 0 end) S, 
sum(case when x.tshirt = 'M' then 1 else 0 end) M, 
sum(case when x.tshirt = 'L' then 1 else 0 end) L,
sum(case when x.tshirt = 'XL' then 1 else 0 end) XL,
sum(case when x.disco = 1 then 1 else 0 end) disco 
from team t 
inner join athlete a on a.team_no = t.team_no
inner join entry_extras x on a.athleteid = x.ath_no
group by t.team_name

So, I should thank r937 for implicitly providing me with the solution.