SitePoint Sponsor |
|
User Tag List
Results 1 to 3 of 3
Hybrid View
-
Jun 14, 2003, 19:47 #1
- Join Date
- Dec 2000
- Location
- The flat edge of the world
- Posts
- 838
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Can I have multiple COUNT() on same table?
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
Work smarter, not harder. -Scrooge McDuck
-
Jun 14, 2003, 22:22 #2
- Join Date
- Dec 2000
- Location
- The flat edge of the world
- Posts
- 838
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
Work smarter, not harder. -Scrooge McDuck
-
Jun 15, 2003, 03:44 #3
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
you're welcome
Bookmarks