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
Solved

SQL for League table

Posted on 2011-09-02
10
1,059 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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
 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
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…

860 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