Football League Total SQL Help

Question

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