SUM() AND COUNT() in same recordset
I have a darts league. I have one table in the database that stores the total scores (results) and one table that holds the individual scores (individResults) and one table that holds the double scores (individResultsDouble). In the league table I need to show the results for each team. It needs to be ordered by overall scores; a win =2 points, a draw =1 point. In the results table I have a column (wld) that stores whether it is a win(w) lose (l) or draw(d).
I have this code which works perfectly:
The problem I have is if the scores are the same It then needs to order the individual results. If an individual teams wins an 'f' will be entered in to the table in the column (fa). If they lose it will be an 'a'. Because I need to combine the singles and doubles table I have this code:
SELECT rTeamID, SUM( IF(wld='w', 2, IF(wld='d', 1, 0 ) ) ) AS wintotal, team.teamName
FROM results LEFT JOIN team ON team.teamID=rTeamID
GROUP BY rTeamID
ORDER BY wintotal DESC
I somehow need to combine these 2 queries into 1 but I just can't seem to do it I need to order it by overall score first and then by individual scores. The rTeamID in the first query and the iTeamID in the second query is the team ID number.
SELECT x.iTeamID, COUNT(x.fa)
FROM ((SELECT a.iTeamID, a.fa FROM individResults AS a
WHERE a.fa='f') UNION ALL (SELECT b.iTeamID, b.fa FROM individResultsDoubles AS b WHERE b.fa='f')) AS x
GROUP BY x. iTeamID