SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    midnight coder
    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
    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
    Work smarter, not harder. -Scrooge McDuck

  2. #2
    midnight coder
    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
    So, I should thank r937 for implicitly providing me with the solution.
    Work smarter, not harder. -Scrooge McDuck

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you're welcome



Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •