How to create multiple MySQL counts / group by / subqueries?

Question

I have a table in my database containing football results, the relevant columns being matchNumber (a number based on the date), Result (either "W", "D" or "L") goalsFor, goalsAg and manager.

I have this query:

SELECT COUNT(*) AS P,  
(SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "W") AS W,  
(SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "D") AS D,  
(SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "L") AS L,  
SUM(GoalsFor) AS F,  
SUM(GoalsAg) AS A,  
ROUND((SELECT SUM((((SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "W") * 2) +(SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "D")))/((SELECT COUNT(*) FROM match_results WHERE manager = 13)*2)*100),2) AS pct  
FROM match_results WHERE manager = 13;

which returns the following result:

P   W  D  L  F   A   Pct  
213 92 49 72 296 247 54.69

This works perfectly. However, the results span a number of seasons and I want to break the output of the query down to reflect this. I have tried the following:

SELECT LEFT(matchNumber,4) AS Season,  
COUNT(*) AS P,  
(SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "W") AS W,  
(SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "D") AS D,  
(SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "L") AS L,  
SUM(GoalsFor) AS F,  
SUM(GoalsAg) AS A,  
ROUND((SELECT SUM((((SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "W") * 2) +(SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "D")))/((SELECT COUNT(*) FROM match_results WHERE manager = 13)*2)*100),2) AS pct  
FROM match_results WHERE manager = 13  
GROUP BY Season;

The outcome of this query can be seen in this image:

http://www.kidderminsterharriers.com/images/query2.jpg

As you can see, the 'Season', 'P', 'F' and 'A' columns have given me the result that I want but the remaining columns are repeating the totals. My question, therefore, is how can I adapt my query so that the 'W', 'D' and 'L' columns also give me year-by-year totals in each line of the output and not the total?

Answer 1

The problem is your three subqueries do not limit their count by season. Try changing it to:

SELECT @season:=LEFT(matchNumber,4) AS Season,  
COUNT(*) AS P,  
(SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "W" AND LEFT(matchNumber,4)=@season) AS W,  
(SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "D" AND LEFT(matchNumber,4)=@season) AS D,  
(SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "L" AND LEFT(matchNumber,4)=@season) AS L,  
SUM(GoalsFor) AS F,  
SUM(GoalsAg) AS A,  
ROUND((SELECT SUM((((SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "W") * 2) +(SELECT COUNT(*) FROM match_results WHERE manager = 13 AND Result = "D")))/((SELECT COUNT(*) FROM match_results WHERE manager = 13)*2)*100),2) AS pct  
FROM match_results WHERE manager = 13  
GROUP BY Season;