asked on

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

Kevin Cross

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!

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lowfatspread

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?

Kevin Cross

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.

Lowfatspread

@mwvisa
yes i agree that union or a cross join to a 2 row table is the probable way to process the data...

Enduro1983

ASKER

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

Enduro1983

ASKER

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

Kevin Cross

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.

Kevin Cross

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.

Enduro1983

ASKER

cheers Mwvisa1, I think I have enough to go on now, thanks for your help!!