Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New to the CF scene
    Join Date
    May 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    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
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Mar 2011
    Posts
    10
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    You'll have to show us the actual query you ran to figure that error out.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    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
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    gbarrett (09-20-2012)

  • #7
    New Coder
    Join Date
    Mar 2011
    Posts
    10
    Thanks
    3
    Thanked 0 Times in 0 Posts
    That was it it works perfectly thank you.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •