# 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;
```

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