sql for calculating points for games Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of sql for calculating points for games without wasting too much if your time.

The question is published on by Tutorial Guruji team.

I have a table with football matches.

Table games:

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;

Answer

You can do it using UNION:

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

Demo here

We are here to answer your question about sql for calculating points for games - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji