SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict
    Join Date
    Oct 2003
    Posts
    245
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Couting Win and Lost

    How do I output the number (count) of Win, Lost and TKO (win) from a query?

    In the table I have something like this

    ID | Outcome | Result_Type
    --------------------------------
    1 | Win | W TKO 1
    2 | Win | W TKO 3
    3 | Lost | L TKO 2
    4 | Win | W UD 8

    I would like output to be like this:
    3 Win, 2 TKO (W), 1 Lost


    SELECT count(Outcome) as 'TKO W' FROM record Where ResultType Like 'W TKO%'
    SELECT count(Outcome) as W FROM record Where Outcome = 'Win'
    SELECT count(Outcome) as L FROM record Where Outcome = 'Lost'

    How do use this 3 query into a single query?

    Thanks
    [Home Sweet Home]

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    If you wanted to count the number of times a value is present in one column of the table (in this case Win and Lost in the Outcome column), you could've used GROUP BY and COUNT, and it would've given you two rows, Win and Lost, and the number of rows for each.

    But, since you want to count occurances in two different columns, that won't work. You could do a UNION ALL of the three queries, or try something like this:
    Code SQL:
    SELECT
        SUM(CASE WHEN ResultType LIKE 'W TKO%' THEN 1
                 ELSE 0
            END) AS 'TKO W'
      , SUM(CASE WHEN Outcome = 'Win' THEN 1
                 ELSE 0
            END) AS W
      , SUM(CASE WHEN Outcome = 'Lost' THEN 1
                 ELSE 0
            END) AS L
    FROM record

  3. #3
    SitePoint Addict
    Join Date
    Oct 2003
    Posts
    245
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you that does work

    But have tried this SQL Statement and it work:

    Code MySQL:
     SELECT 
     sum(Outcome = 'Win') as Win,
     sum(ResultType Like 'W TKO %') As Win_TKO,
     sum(Outcome = 'Lost')  As Lost,
     Count(Outcome)  As Total
     From record

    May I ask why are you using Case/Else statement?

    Can you please show example of using GROUP BY even that wouldn't work
    [Home Sweet Home]

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Shahid View Post
    May I ask why are you using Case/Else statement?
    because CASE is standard SQL, whereas putting an expression into the SUM function is non-standard and will not work in all databases, despite the fact that it does work in mysql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •