0

I want to calculate the points of each team in the PL

I have two tables

T1

---------------------
TEAM ID || TEAM NAME
---------------------
01      || Liverpool
02      || Man City
---------------------

while t2 for instance 

----------------------------------------------------------------
MATCH ID || HOME TEAM || AWAY TEAM || HOME GOALS || AWAY GOALS|
-----------------------------------------------------------------
30       || Liverpool || Man City  || 1          || 0 
-----------------------------------------------------------------

To calculate the points now for each match if a team has num of goals greater than the other team he won and he got 3 Points and the loser got 0 points BUT if each one of them got the same num of goals they're even and each one of them got just 1 point.

the new table should be like this

-----------------------------------
Team ID || Team Name || Team Points
------------------------------------
01      || Liverpool || 28
02      || Man City  || 22
------------------------------------
Improve this question
2
  • 2
    Your second table should be storing TEAM ID for AWAY TEAM and HOME TEAM instead of the team names so it could be easily joined to table 1. Commented Oct 28, 2019 at 12:43
  • Yeah, but it doesn't. Look, I tried to break down t2 into two separate tables in the inner join (t2 H for host and t2 G for Guests) the point is :: is it possible to make sum(case when H.HomeGoals> G.GuestGoals then 3 else 0) etc ..?? I'm afraid it's not right Commented Oct 28, 2019 at 12:48

4 Answers 4

Reset to default
2

Join the tables and use conditional aggregation:

select t1.teamid, t1.teamname,
  sum(
    case sign((homegoals - awaygoals) * case when t1.teamname = t2.hometeam then 1 else -1 end)
      when 1 then 3
      when 0 then 1
      when -1 then 0
    end
  ) teampoints
from t1 inner join t2
on t1.teamname in (t2.hometeam, t2.awayteam)
group by t1.teamid, t1.teamname

See the demo.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks a lot forpas, for help and for your time. how clean. Such a great way of thinking
Glad it helped.
The condition in the ON clause is this: on t1.teamname in (t2.hometeam, t2.awayteam), so there are 2 cases only: t1.teamname = t2.hometeam or t1.teamname = t2.awayteam. This: is why case when t1.teamname = t2.hometeam then 1 else -1 end check only 2 cases. This 1 or -1 is a factor multiplied to the difference of goals.
Yes. Thanks for the explanation, forbas.
1

I would suggest a lateral join:

select t1.team_id, t1.team_name, sum(v.goals),
       sum(case when goals > other_goals then 3 
                when goals = other_goals then 1
                else 0
           end) as points
from t2 cross join lateral
     (values (t2.home_team, t2.home_goals,  t2.away_goals),
             (t2.away_team, t2.away_goals, t2.home_goals)
     ) v(team, goals, other_goals) join
     t1
     on v.team = t1.team_id
group by t1.team_id, t1.team_name;
Improve this answer

3 Comments

Such a wonderful solution Gordon, but how would you calculate the points?
@Nash . . . The same way. It wasn't clear to me when I first read the question that "points" are different from "scores".
Such a fascinating piece of code. but we don't need Sum (V. Goals) according to what the new table should be. yet, it's great work. thanks for help.
0

You can do join the table and do a conditional sum:

select
    t1.team_id,
    t1.team_name,
    sum(
        case 
            when t2.home_goals = t2.away_goals then 1
            when 
                ( t1.team_name = t2.home_team and t2.home_goals > t2.away_team)
                or (t1.team_name = t2.away_team and t2.away_goals > t2.home_team)
                then 3
            else 0 
        end
    ) team_points
from t1
inner join t2 on t1.team_name in (t2.home_team, t2.away_team)
group by t1.team_id, t1.team_name

In the sum(), the case expression checks the outcome of the game and assigns points as needed (3 points for a winning game, 1 point for a draw, 0 points for a loss).

Note: as it has been said in the comments, you should modifiy your schema to store the ids of the teams in the scores table instead of their names.

Improve this answer

4 Comments

Thanks a lot GMB, for help and for your time.
But there's no goals in t1 !, I think you wanted to say `` When t2.home_goals = t2.guest_goals then 1`` right?
@Nash: you are correct. I just modified the query in my answer.
I think, you wanted to say, too: ``When(t1.team_name = t2.home_team and t2.home_goals > t2.away_goals), I mean the last column is for goals not name, Right?
0

Now, what about this

SELECT t1.teamid,
        t1.teamname,
        SUM (CASE   WHEN    t1.teamname = t2.hometeam
                            AND     t2.homegoals > t2.awaygoals THEN    3
                    WHEN    t1.teamname =   t2.awayteam
                            AND     t2.homegoals < t2.awaygoals THEN    3
                    WHEN    t1.teamname IN (t2.hometeam, t2.awayteam)
                            AND     t2.homegoals = t2.awaygoals THEN    1
                    ELSE    0
                    END
             )AS    "Team Points"

FROM    t1
        INNER JOIN
        t2
        ON
        t1.teamname IN (t2.hometeam, t2.awayteam)

GROUP BY t1.teamid,
        t1.teamname

Here's the Demo

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.