Solved

SQL for League table

Posted on 2011-09-02
10
985 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:Enduro1983
  • 5
  • 3
  • 2
10 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36473541
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 36473564
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36473831
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36474230
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36474619
@mwvisa
yes i agree that union or a cross join to a 2 row table is the probable way to process the data...
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Enduro1983
ID: 36488286
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
 

Author Comment

by:Enduro1983
ID: 36494106
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36495202
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36495211
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
 

Author Comment

by:Enduro1983
ID: 36495568
cheers Mwvisa1, I think I have enough to go on now, thanks for your help!!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now