SQL for League table

Hi,

This information will be presented on an asp.net web page using VB.Net

I Need some advice and possibly code snippets, also, whether this is feasible in SQL or should I do some in SQL and then some with VB.Net on the actual site.

I need to produce a league standings table from a SQL table that contains columns relating to;

Matchdate, seasonYear, HomeTeamTitle, HomeGoals, AwayTeamTitle, AwayGoals, Result

1.)      Result Selection - Select all results for selected year from season field
2.)      Points Distribution - Award points to relevant teams e.g. home win awards 3 points to home team, away win awards 3 points to away team, a draw awards 1 point to both teams
3.)      Sum points for each team – Use a method to calculate total for each team for that season from all the results provided by query
4.)      Sort Results (generate table) – Generate a league table of teams and points, the team with the highest points at top, the team with the lowest at bottom


I am ok at select queries and the group by function but not sure about point 2 of how to determine the points and then append that to the relevant teams name that can be then used in the league table??

Thanks

Enduro1983Asked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Okay, it would help if I took just one more sip of coffee. Sorry for all the MySQL references/syntax. Oy.

Anywhere you see `season` or `a name` think [tablename].
UNION reference is on MSDN: http://msdn.microsoft.com/en-us/library/ms180026.aspx
Updated code attached.
DECLARE @seasonYear INT;
SET @seasonYear = 2011;

/*
Can also sum(goals) or avg(goals).
If desired, add column that has OppositionGoals;
   then you can sum() or avg() other team's goals -
   showing a total or average points for/against.
*/
SELECT TeamTitle, SUM(Points) AS TotalPoints
FROM (

-- first get all the home team statistics 
SELECT Matchdate
     , HomeTeamTitle AS TeamTitle
     , HomeGoals AS Goals
     , Result
     , CASE 
          WHEN HomeGoals > AwayGoals THEN 3
          WHEN HomeGoals = AwayGoals THEN 1
          ELSE 0
       END AS Points
FROM [season]
WHERE seasonYear = @seasonYear

UNION ALL

-- second get all the away team statistics 
SELECT Matchdate
     , AwayTeamTitle AS TeamTitle
     , AwayGoals AS Goals
     , Result
     , CASE 
          WHEN AwayGoals > HomeGoals THEN 3
          WHEN AwayGoals = HomeGoals THEN 1
          ELSE 0
       END AS Points
FROM [season]
WHERE seasonYear = @seasonYear

) derived
GROUP BY TeamTitle
ORDER BY TotalPoints DESC
;

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Hi.

Sounds like this is a standings table, so my first thought would be to normalize the data using UNION [ALL]. This allows you to handle point 2.

Here are some notes/clarification:
1.)      Result Selection - Select all results for selected year from season field
So, there is a seasonField column also? Or is the table name `season`. Will go with the latter in my example code.

2.)      Points Distribution - Award points to relevant teams e.g. home win awards 3 points to home team, away win awards 3 points to away team, a draw awards 1 point to both teams
Using the UNION operator, we get one running list of team points (and goals for/against) by match.
http://dev.mysql.com/doc/refman/5.5/en/union.html

3.)      Sum points for each team – Use a method to calculate total for each team for that season from all the results provided by query
Using the UNION'd data above, can do a normal GROUP BY and SUM to get totals for season.

4.)      Sort Results (generate table) – Generate a league table of teams and points, the team with the highest points at top, the team with the lowest at bottom
This becomes a simple ORDER BY [TotalPoints] DESC.

So an example solution may look like this:
SET @seasonYear = 2011;

/*
Can also sum(goals) or avg(goals).
If desired, add column that has OppositionGoals;
   then you can sum() or avg() other team's goals -
   showing a total or average points for/against.
*/
SELECT TeamTitle, SUM(Points) AS TotalPoints
FROM (

-- first get all the home team statistics 
SELECT Matchdate
     , HomeTeamTitle AS TeamTitle
     , HomeGoals AS Goals
     , Result
     , CASE 
          WHEN HomeGoals > AwayGoals THEN 3
          WHEN HomeGoals = AwayGoals THEN 1
          ELSE 0
       END AS Points
FROM `season`
WHERE seasonYear = @seasonYear

UNION ALL

-- second get all the away team statistics 
SELECT Matchdate
     , AwayTeamTitle AS TeamTitle
     , AwayGoals AS Goals
     , Result
     , CASE 
          WHEN AwayGoals > HomeGoals THEN 3
          WHEN AwayGoals = HomeGoals THEN 1
          ELSE 0
       END AS Points
FROM `season`
WHERE seasonYear = @seasonYear

) derived
GROUP BY TeamTitle
ORDER BY TotalPoints DESC
;

Open in new window


Hope that helps!
0
 
LowfatspreadCommented:
if this is a real life rather than "excercise" situation then you shouldn't attempt to calculate the result of the
match by comparing goals... rather you should rely on the result column on the table ...

the result column needs to have as a minimum the following values

W -  win  either just win in which case the win loss is in relation to the "Home" team or specifically a
    Homewin value and an awaywin value
[(L) - Loss  see above]
D - Draw
you may also have other values to indicate situations such as forfiet/walk over ...

ideally you would have another (reference) table which decoded this value into the actual points awarded

you should also have somewhere stored the possibility of penalty points awarded by the league
which would alter the order of the league table result... (e.g. bankruptcy -15pts is a common penalty applied in professional leagues)

....

is this "home/course"work?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Kevin CrossChief Technology OfficerCommented:
LFS, nice thoughts. You can have a game end in a tie and then a winner is decided in shootout, so Result is better. My point was more that UNION could be used to accomplish the task. Given LFS's question on this being homework, give it a try yourself and we can guide you...even if it is not, that is the better approach so you can learn more.
0
 
LowfatspreadCommented:
@mwvisa
yes i agree that union or a cross join to a 2 row table is the probable way to process the data...
0
 
Enduro1983Author Commented:
Hi Guys,

Thanks for the replies, I am going to spend some time over the next day, so I will report my findings when I have tried to implement the query!! Then I will award some points!

Cheers

 
0
 
Enduro1983Author Commented:
Hi mwvisa1,

Your solution looks just like what I am looking for, I like the structure, just a couple of things when I run it I get an error "The Declare SQL construct or statement is not supported."  

but adding on from that I would also need the season value to be passed from a drop down on a web page, would you use a create parameter in the query???

Cheers
0
 
Kevin CrossChief Technology OfficerCommented:
Yes, a parameter would make sense and is the reason I showed with declare. Remove the declare and set line. Create that and other parameters on your web application. You would then just run the SELECT from your app code passing the parameters you need.
0
 
Kevin CrossChief Technology OfficerCommented:
Additionally, my formula for distributing points was just an example, so you should try your hand at it using LFS's suggestion at looking at results given soccer's winner can often b determined by other factor(s) than goals, e.g., shootout.
0
 
Enduro1983Author Commented:
cheers Mwvisa1, I think I have enough to go on now, thanks for your help!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.