Football League Table Totals. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Football League Table Totals.

Author

Topic

Boldonglen
Starting Member

11 Posts

Posted - 2011-12-01 : 09:54:30

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 10:00:23

as of now what you're having in your table is one record per user per date with upto 4 results stred and total of them. if you want to sum totals of user per week, what you need is


SELECT USER_ID,DATEADD(wk,DATEDIFF(wk,0,RESULT_DATE),0) AS weekdate,SUM(RESULT_TOTAL) AS WeekTotal
FROM table
GROUP BY USER_ID,DATEADD(wk,DATEDIFF(wk,0,RESULT_DATE),0)

you can put above results in a new table and then compare the WeekTotal with actual total to determine the winners

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Boldonglen
Starting Member

11 Posts

Posted - 2011-12-01 : 10:09:19

quote:
Originally posted by visakh16

as of now what you're having in your table is one record per user per date with upto 4 results stred and total of them. if you want to sum totals of user per week, what you need is

SELECT USER_ID,DATEADD(wk,DATEDIFF(wk,0,RESULT_DATE),0) AS weekdate,SUM(RESULT_TOTAL) AS WeekTotal
FROM table
GROUP BY USER_ID,DATEADD(wk,DATEDIFF(wk,0,RESULT_DATE),0)

you can put above results in a new table and then compare the WeekTotal with actual total to determine the winners

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WOW just like to say a huge thanks for a very quick responce. This has worked out great for me thank you. Do you think it would be reasonable for me to create a new table called LEAGUE_RESULTS and save the information from this SQL statement into there then i can use ASP.NET code to show the leaderboard for the week by using the weekdate collumn?

Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 10:11:24

yep. that should be fine. You can have an additional column in that table to designate whether that user emerged as a winner. populate table using above select except win flag column. Update that column later after comparing the WeekTotal value with actual value.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Boldonglen
Starting Member

11 Posts

Posted - 2011-12-01 : 10:16:37

quote:
Originally posted by visakh16

yep. that should be fine. You can have an additional column in that table to designate whether that user emerged as a winner. populate table using above select except win flag column. Update that column later after comparing the WeekTotal value with actual value.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi sorry i have just realised that this SQL statement is not giving me the results i would like. What i need is the totals of all of the RESULTS_TOTAL for each user. For example user 1002 has the following results:

162 1002 15/11/2011 0 0 0 0 0
165 1002 18/11/2011 1 1 0 0 2
170 1002 02/11/2011 1 1 1 1 4

so the results i would expect to see would be User_ID: 1002 TOTAL_RESULT: 6.

I would not need a date as all of the results are added together they do not depend on the date field.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 10:19:31

[code]
SELECT USER_ID,SUM(RESULT_TOTAL) AS TotalScore
FROM table
GROUP BY USER_ID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Boldonglen
Starting Member

11 Posts

Posted - 2011-12-01 : 10:21:56

quote:
Originally posted by visakh16

SELECT USER_ID,SUM(RESULT_TOTAL) AS TotalScore
FROM table
GROUP BY USER_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

OMG its thast simple.

Thanks so much you have been a big help.

I know where to come now if i need any SQL help. :D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 10:25:44

no problem
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Boldonglen
Starting Member

11 Posts

Posted - 2011-12-01 : 11:01:02

quote:
Originally posted by visakh16

no problem
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ok so this all works fine howeer i would like to create a link on my ASP page that runs a script to update the new table LEAGUE_TABLE with the results every week. The only way i can think of is a SQL statement along the lins of:

UPDATE LEAGUE_TABLE SET USER_ID, TOTAL_SCORE
(SELECT USER_ID, SUM(RESULTS_TOTAL) AS TOTAL_SCORE
FROM RESULTS
GROUP BY USER_ID)
WHERE RESULTS.USER_ID = LEAGUE_TABLE.USER_ID

Could you let me know how i could do this.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 11:04:28

quote:
Originally posted by Boldonglen

quote:
Originally posted by visakh16

no problem
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ok so this all works fine howeer i would like to create a link on my ASP page that runs a script to update the new table LEAGUE_TABLE with the results every week. The only way i can think of is a SQL statement along the lins of:

UPDATE LEAGUE_TABLE SET USER_ID, TOTAL_SCORE
(SELECT USER_ID, SUM(RESULTS_TOTAL) AS TOTAL_SCORE
FROM RESULTS
GROUP BY USER_ID)
WHERE RESULTS.USER_ID = LEAGUE_TABLE.USER_ID

Could you let me know how i could do this.

Thanks

update should be like


UPDATE l
SET l.TOTAL_SCORE = r.TOTAL_SCORE
FROM LEAGUE_TABLE  l
INNER JOIN
(SELECT        USER_ID, SUM(RESULTS_TOTAL) AS TOTAL_SCORE
FROM            RESULTS
GROUP BY USER_ID) r
ON r.USER_ID = l.USER_ID

i didnt understand the need of link in asp page though

you can configure this to happen automatically each week by keeping this script inside a sql server agent job and scheduling it weekly

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Boldonglen
Starting Member

11 Posts

Posted - 2011-12-01 : 11:20:44

quote:
Originally posted by visakh16

quote:
Originally posted by Boldonglen

quote:
Originally posted by visakh16

no problem
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ok so this all works fine howeer i would like to create a link on my ASP page that runs a script to update the new table LEAGUE_TABLE with the results every week. The only way i can think of is a SQL statement along the lins of:

UPDATE LEAGUE_TABLE SET USER_ID, TOTAL_SCORE
(SELECT USER_ID, SUM(RESULTS_TOTAL) AS TOTAL_SCORE
FROM RESULTS
GROUP BY USER_ID)
WHERE RESULTS.USER_ID = LEAGUE_TABLE.USER_ID

Could you let me know how i could do this.

Thanks

update should be like

UPDATE l
SET l.TOTAL_SCORE = r.TOTAL_SCORE
FROM LEAGUE_TABLE l
INNER JOIN
(SELECT USER_ID, SUM(RESULTS_TOTAL) AS TOTAL_SCORE
FROM RESULTS
GROUP BY USER_ID) r
ON r.USER_ID = l.USER_ID

i didnt understand the need of link in asp page though

you can configure this to happen automatically each week by keeping this script inside a sql server agent job and scheduling it weekly

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

This is giving me the error:

Column or expression 'TOTAL_SCORE' cannot be updated.

And then saying:

Invalid object name 'l'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 11:40:31

have you added that column in LEAGUE_TABLE ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Boldonglen
Starting Member

11 Posts

Posted - 2011-12-01 : 11:41:37

quote:
Originally posted by visakh16

have you added that column in LEAGUE_TABLE ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes i have the columns within LEAGUE_TABLE are USER_ID, TOTAL_SCORE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 11:43:47

you should have a column to store total amount in league table. thats column you should give in update statement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Boldonglen
Starting Member

11 Posts

Posted - 2011-12-01 : 11:45:04

quote:
Originally posted by visakh16

you should have a column to store total amount in league table. thats column you should give in update statement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes i do the colums i have within LEAGUE_TABLE are USER_ID, TOTAL_SCORE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 11:47:53

quote:
Originally posted by Boldonglen

quote:
Originally posted by visakh16

you should have a column to store total amount in league table. thats column you should give in update statement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes i do the colums i have within LEAGUE_TABLE are USER_ID, TOTAL_SCORE

then this should work


UPDATE l
SET l.TOTAL_SCORE = r.TOTAL_SCORE
FROM LEAGUE_TABLE  l
INNER JOIN
(SELECT        USER_ID, SUM(RESULTS_TOTAL) AS TOTAL_SCORE
FROM            RESULTS
GROUP BY USER_ID) r
ON r.USER_ID = l.USER_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Boldonglen
Starting Member

11 Posts

Posted - 2011-12-01 : 11:56:50

quote:
Originally posted by visakh16

quote:
Originally posted by Boldonglen

quote:
Originally posted by visakh16

you should have a column to store total amount in league table. thats column you should give in update statement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes i do the colums i have within LEAGUE_TABLE are USER_ID, TOTAL_SCORE

then this should work

UPDATE l
SET l.TOTAL_SCORE = r.TOTAL_SCORE
FROM LEAGUE_TABLE l
INNER JOIN
(SELECT USER_ID, SUM(RESULTS_TOTAL) AS TOTAL_SCORE
FROM RESULTS
GROUP BY USER_ID) r
ON r.USER_ID = l.USER_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

It is still not working and when i run this script within the server section of Visual Studio it is chaning the SQL script to the following:

UPDATE l
SET l.TOTAL_SCORE = r.TOTAL_SCORE
FROM LEAGUE_TABLE AS l INNER JOIN
(SELECT USER_ID, SUM(RESULTS_TOTAL) AS TOTAL_SCORE
FROM RESULTS
GROUP BY USER_ID) AS r ON r.USER_ID = l.USER_ID CROSS JOIN
l

Also it shows the tables at the top of the screen and it is showing:

l
[No fields]

r
[USER_ID]
[TOTAL_SCORE]

l
[USER_ID]
[TOTAL_SCORE]

With a relationship link between r and the l (with columns inside)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 12:02:08

server section of Visual Studio?? why should someone do that. You should be running this in your default sql query editor in sql server management studio, not anywhere else

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Boldonglen
Starting Member

11 Posts

Posted - 2011-12-01 : 12:05:48

quote:
Originally posted by visakh16

server section of Visual Studio?? why should someone do that. You should be running this in your default sql query editor in sql server management studio, not anywhere else

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Visual Studio 2010 has a section for creating databases and quiering them. It works just like a sql query editor.

What could i be dowin wrong within the SQL?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 12:08:09

i dont know about that editor but you should be ideally using sql management studio which supports the full t-sql syntax

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Boldonglen
Starting Member

11 Posts

Posted - 2011-12-01 : 12:10:12

quote:
Originally posted by visakh16

i dont know about that editor but you should be ideally using sql management studio which supports the full t-sql syntax

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ok thanks but i honestly do not think that this would change the results i am getting.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources