Couting Win and Lost

[PowerStrike]

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

[guido2004]

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:

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

[PowerStrike]

Thank you that does work

But have tried this SQL Statement and it work:

 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

[r937]

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