# SUM() AND COUNT() in same recordset

• 09-18-2012, 01:02 PM
gbarrett1
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:
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```
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:

Code:

```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```
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.
• 09-19-2012, 12:02 AM
Old Pedant
I *think* this does it:
Code:

``` SELECT team.TeamID, team.teamName, allresults.wintotal, allresults.individualWins FROM team LEFT JOIN (     ( SELECT rTeamID, SUM( IF(wld='w', 2, IF(wld='d', 1, 0 ) ) ) AS wintotal       FROM results       GROUP BY rTeamID ) AS tr     INNER JOIN     ( SELECT x.iTeamID, COUNT(*) AS individualWins       FROM (               SELECT iTeamID FROM individResults WHERE fa='f'               UNION ALL               SELECT iTeamID FROM individResultsDoubles WHERE fa='f'             ) AS x       GROUP BY x.iTeamID     ) AS ir     ON tr.rTeamID = ir.iTeamID   ) AS allresults ON team.teamID = allresults.rTeamID ORDER BY allresults.wintotal, allresults.individualWins```
• 09-19-2012, 11:51 AM
gbarrett
Wow, thank you - I can see all the logic but I am getting this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'allresults ON team.TeamID = allresults.rTeamID ORDER BY allresults.wintotal, all' at line 16
• 09-19-2012, 04:11 PM
You'll have to show us the actual query you ran to figure that error out.
• 09-20-2012, 12:42 AM
Old Pedant
It may just be MySQL being stupid, yet again.

One thing that might help is to convert some of those inner queries into VIEWs and then join the views.

For example:
Code:

```CREATE VIEW v1 AS SELECT rTeamID, SUM( IF(wld='w', 2, IF(wld='d', 1, 0 ) ) ) AS wintotal FROM results GROUP BY rTeamID; CREATE VIEW v2 AS SELECT iTeamID FROM individResults WHERE fa='f' UNION ALL SELECT iTeamID FROM individResultsDoubles WHERE fa='f'; CREATE VIEW v3 AS SELECT iTeamID, COUNT(*) AS individualWins FROM v2 GROUP BY iTeamID; CREATE VIEW allresults AS SELECT * FROM v1 INNER JOIN v3 ON v1.rTeamID = v3.iTeamID; SELECT team.TeamID, team.teamName, allresults.wintotal, allresults.individualWins FROM team LEFT JOIN allResults ON team.TeamID = allresults.rTeamID ORDER BY allresults.wintotal, allresults.individualWins```
(You can and should use better view names than just v1, v2, v3.)

IF you do that, then you can test each view independently first:
Code:

```SELECT * FROM v1; SELECT * FROM v2; SELECT * FROM v3; SELECT * FROM allresults;```
If they all look good, then the final query should be fine.
• 09-20-2012, 12:44 AM
Old Pedant
You know, looking at all those views, I think I see the problem in my original query.

I think it is missing the SELECT * that I have in the allresults VIEW.

So maybe this is all that needs added:
Code:

```SELECT team.TeamID, team.teamName, allresults.wintotal, allresults.individualWins FROM team LEFT JOIN (     SELECT * FROM     ( SELECT rTeamID, SUM( IF(wld='w', 2, IF(wld='d', 1, 0 ) ) ) AS wintotal       FROM results       GROUP BY rTeamID ) AS tr     INNER JOIN     ( SELECT x.iTeamID, COUNT(*) AS individualWins       FROM (               SELECT iTeamID FROM individResults WHERE fa='f'               UNION ALL               SELECT iTeamID FROM individResultsDoubles WHERE fa='f'             ) AS x       GROUP BY x.iTeamID     ) AS ir     ON tr.rTeamID = ir.iTeamID   ) AS allresults ON team.teamID = allresults.rTeamID ORDER BY allresults.wintotal, allresults.individualWins```
• 09-20-2012, 12:23 PM
gbarrett
That was it it works perfectly thank you.