Hi Guys,
I have been creating a website that allows users to predict the football (soccer) results and save them within a database. When the results of the football matches have been announced i update them on the website and then all of the users predictios are cross checked against the actual results and if they have the same score they get a point and if they dont the get 0 points. These points are saved within a table called Results. What im having problem with is creating a league table to show who has the most points from guessing the correct resutls of the games. In my RESULTS table i have RESULT_ID, USER_ID, RESULT_DATE, RESULT1, RESULT2, RESULT3, RESULT4, RESULT_TOTAL. This table joins to my users table using the USER_ID. So far im saving their results by week so for each week (RESULT_DATE) they have a set of results (RESULT1-4) and then all of the results (RESULT1-4) are added together and saved within the RESULTS_TOTAL column. What i would like to do is to create a simple league table that shows a list of Users along with their RESULTS_TOTAL however since they have multiple totals (as they have made multiple predictions (RESULT_DATE)) i am unsure how i would add all of these totals up and place them within either another table or place them into a DataTable within C# .NET.
Below shows what my RESULTS table looks like with some sample data to try and explain a little more.
RESULT_ID USER_ID RESULT_DATE RESULT1 RESULT2 RESULT3 RESULT4 RESULT_TOTAL
| 160 | 1001 | 15/11/2011 | 0 | 0 | 0 | 0 | 0 |
| 161 | 1000 | 15/11/2011 | 0 | 0 | 0 | 0 | 0 |
| 162 | 1002 | 15/11/2011 | 0 | 0 | 0 | 0 | 0 |
| 163 | 1003 | 18/11/2011 | 1 | 1 | 1 | 1 | 4 |
| 164 | 1000 | 18/11/2011 | 0 | 1 | 1 | 1 | 3 |
| 165 | 1002 | 18/11/2011 | 1 | 1 | 0 | 0 | 2 |
| 166 | 1000 | 17/11/2011 | 0 | 0 | 0 | 0 | 0 |
| 167 | 1000 | 05/11/2011 | 1 | 0 | 0 | 0 | 1 |
| 168 | 1000 | 02/11/2011 | 0 | 0 | 0 | 0 | 0 |
| 169 | 1001 | 02/11/2011 | 0 | 0 | 0 | 0 | 0 |
| 170 | 1002 | 02/11/2011 | 1 | 1 | 1 | 1 | 4 |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
If you would like anymore information please feel free to ask.
Thanks
Boldonglen