Mateobus Posted March 13, 2007 Share Posted March 13, 2007 Very Tough Query Hey all I have a tough query here. Basically I am making a top 25 poll, and I am trying to use two tables. The first table is the votes table and is set up as follows: voterid | place | teamid The second table should contain the number of points for each team from the votes table as well as the number of first place votes. Points can be calculated by the equation 26-place. So a first place vote gets 25 points, 2nd 24... The second table is set up as follows teamid | points | first_place_votes The query should incorporate all of the votes from the first table into the second one. If anyone can help I would greatly appreciate it. I could use php to rig this up with arrays and such, but I would rather have it done more efficiently. Quote Link to comment Share on other sites More sharing options...
shoz Posted March 13, 2007 Share Posted March 13, 2007 Very Tough Query Hey all I have a tough query here. Basically I am making a top 25 poll, and I am trying to use two tables. The first table is the votes table and is set up as follows: voterid | place | teamid The second table should contain the number of points for each team from the votes table as well as the number of first place votes. Points can be calculated by the equation 26-place. So a first place vote gets 25 points, 2nd 24... The second table is set up as follows teamid | points | first_place_votes The query should incorporate all of the votes from the first table into the second one. If anyone can help I would greatly appreciate it. I could use php to rig this up with arrays and such, but I would rather have it done more efficiently. You can get this output dynamically. If there are performance issues because of how frequent the page is being accessed or because of how much data there is, then caching the page that shows the output should be a viable option. To answer your question INSERT INTO table2 (teamid, points, first_place_votes) SELECT teamid, SUM(26 - place) AS points, SUM(place = 1) AS first_place_votes FROM table1 GROUP BY teamid Quote Link to comment Share on other sites More sharing options...
artacus Posted March 13, 2007 Share Posted March 13, 2007 So lets see if I understand... a single voter is going to vote for (up to) 25 teams? It looks like shoz has a pretty good handle on it. But I'd recommend you fill table two with your teams, 0 points and 0 first place votes and then after every vote, run what shoz gave you as an update query. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.