SQL for League table
Posted on 2011-09-02
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??