SUM() AND COUNT() in same recordset

[gbarrett1]

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:

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:

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.

[Old Pedant]

I *think* this does it:

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

[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

[guelphdad]

You'll have to show us the actual query you ran to figure that error out.

[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:

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:

SELECT * FROM v1;
SELECT * FROM v2;
SELECT * FROM v3;
SELECT * FROM allresults;

If they all look good, then the final query should be fine.

[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:

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

[gbarrett]

That was it it works perfectly thank you.