CODEWITHSUNDEEP
sqlpostgresql
user12285060
user12285060

Reputation:

SQL Complex Conditional Aggregation

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
------------------------------------

Upvotes: 0

Views: 135

Answers (4)

user12285060
user12285060

Reputation:

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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;

Upvotes: 1

GMB
GMB

Reputation: 222402

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.

Upvotes: 0

forpas
forpas

Reputation: 164064

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.

Upvotes: 2

Related Questions