Doing more than one COUNT on the same column in one php script SELECT query

Question

I have two columns 'Team' and 'WLD' in a mysql database. WLD stands for Won, Lost, Drawn and contains either a W, L or D on every line of hundreds. I want to output how many W's, L's and D's there are in the column in one SELECT.

If I do this with one of them it works:

SELECT Team, COUNT(WLA) AS Won FROM ResultsTest WHERE WLA = 'W'

However, as soon as I add another COUNT statement it starts to complain:

SELECT Team, COUNT(WLA) AS Won 
FROM ResultsTest 
WHERE WLA = 'W', 
COUNT(WLA) AS Lost FROM ResultsTest WHERE WLA = 'L'

Where am I going wrong?

SQL does not have such syntax. In cases like this you need to use `CASE` and emulate `COUNT` with `SUM`. — PM 77-1, Mar 12 '18 at 14:13
Have a look at [conditional sum](https://stackoverflow.com/questions/16517298/conditional-sum-in-group-by-query-mssql) — pritaeas, Mar 12 '18 at 14:14
Possible duplicate of [Conditional Count on a field](https://stackoverflow.com/questions/1288058/conditional-count-on-a-field) — PM 77-1, Mar 12 '18 at 14:15
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) are you using? "SQL" is just a query language, not the name of a specific database product. Please add the tag for the database product you are using `postgresql`, `oracle`, `db2`, `sql-server`, ... — , Mar 13 '18 at 21:13

score 0 · Answer 1 · answered Mar 13 '18 at 20:06

You need a GROUP BY clause in order finalize your data aggregation. On the top of that, if you don't want to create two distinct extractions for wins and losses and join them, you can use a binary sum in cases:

SELECT Team,
       COUNT(*) AS Matches,
       SUM(CASE WHEN WLD = 'W' THEN 1 ELSE 0 END) AS Wins,
       SUM(CASE WHEN WLD = 'L' THEN 1 ELSE 0 END) AS Losses,
       SUM(CASE WHEN WLD != 'W' AND WLD != 'L' THEN 1 ELSE 0 END) AS Draws
FROM ResultsTest
GROUP BY Team

score -1 · Answer 2 · edited Mar 13 '18 at 21:14

-1

Thanks guys. It works. Saved me a lot of time:

SELECT Team, 
       COUNT(*) WLD, 
       SUM(CASE WHEN WLD = 'W' THEN 1 ELSE 0 END) AS Won, 
       SUM(CASE WHEN WLD = 'L' THEN 1 ELSE 0 END) AS Lost, 
       SUM(CASE WHEN WLD = 'D' THEN 1 ELSE 0 END) AS Drawn 
FROM ResultsTest 
GROUP BY Team

edited Mar 13 '18 at 21:14

answered Mar 13 '18 at 19:56

Roy Caswell

1
2

Doing more than one COUNT on the same column in one php script SELECT query

2 Answers2