?
Solved

SQL for League table

Posted on 2011-09-02
10
Medium Priority
?
1,270 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 60

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 60

Accepted Solution

by:
Kevin Cross earned 1000 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 60

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
 

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 60

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 60

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

800 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