CodingForums.com - SUM() AND COUNT() in same recordset

CodingForums.com (http://www.codingforums.com/index.php)

- MySQL (http://www.codingforums.com/forumdisplay.php?f=7)

- - SUM() AND COUNT() in same recordset (http://www.codingforums.com/showthread.php?t=273454)

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.

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

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

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

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.

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

That was it it works perfectly thank you.