unable to group by when using two ctes-CodePudding

with table1 as (
    select club_results.goals_scored as goals_scored, club_results.goals_conceded as goals_conceded  
    from club_results 
    inner join club on club_results.club_id=club.club_id
),
table2 as(
    select (club_results.goals_scored - club_results.goals_conceded) as goal_difference
    from club_results
    inner join club on club_results.club_id=club.club_id
)
select club.club_name, table1.goals_scored, table1.goals_conceded, table2.goal_difference,
case
    when table2.goal_difference > 0 then 3
    when table2.goal_difference < 0 then 0 
    else 1 
    end as points 
from table1, table2, club
group by club.club_name;

I have two ctes and i want to group by the club_name but as all the other columns are not aggregated it is not allowing me to do so. I cannot figure out another way of doing it, potentially a subquery but wouldn't the same issue occur again?

The aim is to create a table of this nature but group by club_names.

CodePudding user response：

If I follow you correctly, we can do this without CTEs, by properly aggregating the results table:

select c.club_name,
    sum(cr.goals_scored) as goals_scored,
    sum(cr.goals_conceded) as goals_conceded,
    sum(cr.goals_scored) - sum(cr.goals_conceded) as goal_difference,
    sum(
        case sign(cr.goals_scored - cr.goals_conceded)
            when -1 then 0
            when  0 then 1
            when  1 then 3
        ) as points
from club c
inner join club_results cr on cr.club_id = c.club_id
group by c.club_id

There is a little trick with the use of sign, which we use to compare the goals scored and conceded ; but the important thing is that we need to do the comparison within the aggregate function, so the point computation is repeated for each and every game.

In Posgres we could also have expressed the point computation like this:

count(*) filter(where cr.goals_scored = cr.goals_conceded)
  3 * count(*) filter(where cr.goals_scored > cr.goals_conceded)