# Thread: Couting Win and Lost

1. ## 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&#37;'
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

2. 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. 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

4. Originally Posted by Shahid
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

#### Posting Permissions

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