home_team AS team,
count(case when home_score > away_score then 1 end) wins,
count(case when away_score> home_score then 1 end) losses,
count(case when home_score = away_score then 1 end) draws,
sum(home_score) - sum(away_score) goal_diff,
case when home_score > away_score then 3 else 0 end
+ case when home_score = away_score then 1 else 0 end
select home_team, home_score, away_score from matches
select away_team, away_score, home_score from matches
group by home_team
order by punten desc, goal_diff desc;
team | played_matches | wins | losses | draws | goals_scored | goals_against | goal_diff | points
But now my 4 problems:
- The home and away_team columns are both foreign keys referencing the id on the teams table, so I want to join the team names on the matches so the output displays names instead of the foreign keys.
- Since I have multiple seasons and competitions, I need to filter the matches first on competition and season id. So it only displays matches from season '14-15' and competition '2A' (par example). I can't get the WHERE statement working on this one.
- I have a table with penalty points which also has an season, competition and team id. I want to add the penalty points to the league table so that it get's subtracted form the points column and an extra column that displays them.
- I'd like to add a column with positions that puts 1 to X in a column that displays the rank in the output.
I am by no means an SQL expert, so I can't figure out how to do this.