I have a query that will calculate the win/loss/tie record of teams. I created the SQL query and tested… then put it into a grid.
It will NOT have any editing and only want to list the information. Here is the SQL:
SELECT team.teamkey, team.teamname, SUM( IF( b.teamscore > c.teamscore
AND team.sectionkey = d.sectionkey, 1, 0 ) ) ‘Wins’, SUM( IF( b.teamscore < c.teamscore
AND team.sectionkey = d.sectionkey, 1, 0 ) ) ‘Losses’, SUM( IF( b.teamscore = c.teamscore
AND team.sectionkey = d.sectionkey, 1, 0 ) ) ‘Ties’, SUM( IF( b.teamscore > c.teamscore, 1, 0 ) ) OWins, SUM( IF( b.teamscore < c.teamscore, 1, 0 ) ) OLosses, SUM( IF( b.teamscore = c.teamscore, 1, 0 ) ) OTies
FROM team as team
LEFT JOIN meetmatchup b ON team.teamkey = b.teamkey
LEFT JOIN meetmatchup c ON b.meetkey = c.meetkey
AND b.matchupno = c.matchupno
AND team.teamkey != c.teamkey
LEFT JOIN team d ON d.teamkey = c.teamkey
JOIN meet e ON e.meetkey = b.meetkey
WHERE meettypekey =1
AND team.sectionkey =9
AND YEAR( e.startdate ) =2015
GROUP BY 1 , 2
ORDER BY 3 DESC , OWins DESC
When trying to run it in the grid application I get the following error (even though it runs in SQL builder):
ErrorError while accessing the database:
SelectLimit(SELECT team.teamname as team_teamname, SUM( IF( b.teamscore > c.teamscore AND team.sectionkey = d.sectionkey,1,0 ) ) ‘Wins’ as sum_2, SUM( IF( b.teamscore < c.teamscore AND team.sectionkey = d.sectionkey,1,0 ) ) ‘Losses’ as sum_3, SUM( IF( b.teamscore = c.teamscore AND team.sectionkey = d.sectionkey,1,0 ) ) ‘Ties’ as sum_4, SUM( IF( b.teamscore > c.teamscore,1,0 ) ) OWins as sum_5, SUM( IF( b.teamscore < c.teamscore,1,0 ) ) OLosses as sum_6, SUM( IF( b.teamscore = c.teamscore,1,0 ) ) OTies as sum_7, team.teamkey as team_teamkey from team as team LEFT JOIN meetmatchup b ON team.teamkey = b.teamkey LEFT JOIN meetmatchup c ON b.meetkey = c.meetkey AND b.matchupno = c.matchupno AND team.teamkey != c.teamkey LEFT JOIN team d ON d.teamkey = c.teamkey JOIN meet e ON e.meetkey = b.meetkey where meettypekey =1 AND team.sectionkey =9 AND YEAR( e.startdate ) =2015 group by 1 , 2 order by 3 DESC, OWins DESC, 82, 0)
It seems to be changing it with a ‘SelectLimit’
What do I do?
(Sample Table)
T=1"]eamkey =1"]teamname =1"]Wins =1"]Losses =1"]Ties =1"]OWins =1"]OLosses =1"]OTies134 Washingtonville 11 0 0 11 0 086 Newburgh Free Academy 10 3 0 10 3 0
82 Monroe-Woodbury 9 1 0 10 1 0
