The question is published on by Tutorial Guruji team.
I have a table with football matches.
id team_home (varchar) team_away (varchar) team_home_goals INT team_away_goals INT
To insert I do like this:
insert into games values(null, 'Liverpool', 'Chelsea', 0, 0); insert into games values(null, 'Arsenal', 'City', 1, 2); insert into games values(null, 'ManUTD', 'Tottenham', 2, 1); insert into games values(null, 'Chelsea', 'Arsenal', 0, 0); insert into games values(null, 'Tottenham', 'Liverpool', 1, 0); insert into games values(null, 'City', 'ManUTD', 2, 1);
I need to create a query to get the table with positions. According to the inserts above the table would be:
1 - City 6 points 2 - ManUTD 3 points 3 - Tottenham 3 points 4 - Chelsea 2 points 5 - Liverpool 1 point 6 - Arsenal 1 point
The query I create for this is either for “home teams” or “away teams”. I could run this query twice and then sum the points some where else. But I wonder if there is a smart way to do everything in only one query. This is what I have now (I run this twice, first for home team and then for away team):
select team_home, sum ( case when team_home_goals > team_away_goals then 3 when team_home_goals = team_away_goals then 1 when team_home_goals < team_away_goals then 0 end ) as points from games group by team_home;
You can do it using
SELECT team, SUM(points) AS total_points FROM ( SELECT team_home AS team, CASE WHEN team_home_goals > team_away_goals THEN 3 WHEN team_home_goals = team_away_goals THEN 1 ELSE 0 END AS points FROM games UNION ALL SELECT team_away AS team, CASE WHEN team_away_goals > team_home_goals THEN 3 WHEN team_away_goals = team_home_goals THEN 1 ELSE 0 END AS points FROM games ) AS t GROUP BY team ORDER BY total_points DESC