This query almost works
Posted: Tue May 10, 2011 2:36 am
Would appreciate some help with this one
Have a scoring database (snooker & Billiards) it works well, but am still developing
Have this query
SELECT SUM(IFNULL(Team_entries.total_score,0))/COUNT(Team_entries.team_grade)/Team_entries.players/1.5 + SUM(IFNULL(Team_entries.scr_adjust,0))/COUNT(Team_entries.team_grade)/Team_entries.players/1.5 AS S1total, Team_entries.scr_adjust, Team_entries.team_grade, Team_entries.total_score, Team_entries.players
FROM Team_entries
WHERE Team_entries.team_grade =colname
colname = the value for a particular grade passed from another page (sorry, I use dreamweaver to generate my code)
There are 8 grades competing and the result of this query should tell me if i have inserted the scores for a particular grade and round correctly by giving me a whole number (currently round 11)
My problem was: on occasion a player may forfeit a match, where no points are awarded to either team, for that match. Or a complete team may forfeit a match and incur a penalty (-4). Very difficult to write this into a query so I added the "scr_adjust" column into the table to allow for manual score adjustments.
As I input scores the "total_score" column in the "team_entries" table is updated for a particular team in a particular grade. You can see by the query this is then totalled for a particular grade, then divided by the number of teams in that grade (COUNT(Team_entries.team_grade). Then divided by the number of players in a team (Team_entries.players). All matches (between 2 players) have a total of 3 points available so I then divided by 1.5. (maybe too much information)
While the query works it seems a little cumbersome, and it returns a slightly incorrect result 10.9999999833 (should be 11) but only on some grades.
I can see why it returns the slightly incorrect result, I am in effect using 2 sums and then adding them together.
Not sure how to write it but I think I need to add - "SUM(IFNULL(Team_entries.total_score,0)" + "SUM(IFNULL(Team_entries.scr_adjust,0)" and then divide the outcome of that sum
Any suggestions?
Thanks in advance for any assistance
Have a scoring database (snooker & Billiards) it works well, but am still developing
Have this query
SELECT SUM(IFNULL(Team_entries.total_score,0))/COUNT(Team_entries.team_grade)/Team_entries.players/1.5 + SUM(IFNULL(Team_entries.scr_adjust,0))/COUNT(Team_entries.team_grade)/Team_entries.players/1.5 AS S1total, Team_entries.scr_adjust, Team_entries.team_grade, Team_entries.total_score, Team_entries.players
FROM Team_entries
WHERE Team_entries.team_grade =colname
colname = the value for a particular grade passed from another page (sorry, I use dreamweaver to generate my code)
There are 8 grades competing and the result of this query should tell me if i have inserted the scores for a particular grade and round correctly by giving me a whole number (currently round 11)
My problem was: on occasion a player may forfeit a match, where no points are awarded to either team, for that match. Or a complete team may forfeit a match and incur a penalty (-4). Very difficult to write this into a query so I added the "scr_adjust" column into the table to allow for manual score adjustments.
As I input scores the "total_score" column in the "team_entries" table is updated for a particular team in a particular grade. You can see by the query this is then totalled for a particular grade, then divided by the number of teams in that grade (COUNT(Team_entries.team_grade). Then divided by the number of players in a team (Team_entries.players). All matches (between 2 players) have a total of 3 points available so I then divided by 1.5. (maybe too much information)
While the query works it seems a little cumbersome, and it returns a slightly incorrect result 10.9999999833 (should be 11) but only on some grades.
I can see why it returns the slightly incorrect result, I am in effect using 2 sums and then adding them together.
Not sure how to write it but I think I need to add - "SUM(IFNULL(Team_entries.total_score,0)" + "SUM(IFNULL(Team_entries.scr_adjust,0)" and then divide the outcome of that sum
Any suggestions?
Thanks in advance for any assistance