Finding the sum of the max of a column-CodePudding

I have the following piece of code:

select  sum(max(p.highest_market_value_in_eur)) as value, c.name  from Transfermarket.dbo.players$ p
left join Transfermarket.dbo.player_valuations v
on p.player_id = v.player_id
inner join Transfermarket.dbo.competitions$ c
on c.competition_id = v.player_club_domestic_competition_id
where c.name = 'Ligue 1' and last_season = 2022
group by   c.name;

Apparently, performing aggregate functions on expressions containing an aggregate doesn't work. Is there any other way? I suppose I could use a subquery, but I am not sure how to do that.

CodePudding user response：

So you're going to want to do this in two steps.

If i am understanding your question correctly, you are wanting to find the maximum p.highest_market_value_in_eur for each player in the players table for a given c.name where the last_season is 2022. Then you want sum for all players in that competition.

Lets find our max first:

select
    max(p.highest_market_value_in_eur) as value,
    p.player_id as player_id
from
    Transfermarket.dbo.players$ p
left join Transfermarket.dbo.player_valuations v on p.player_id = v.player_id
inner join Transfermarket.dbo.competitions$ c on c.competition_id = v.player_club_domestic_competition_id
where c.name = 'Ligue 1' and last_season = 2022
group by p.player_id;

The above query will find the max value of p.highest_market_value_in_eur for each player where the competition name is 'Ligue 1' and the last season is 2022. Notice that the first step here is grouping on a different value. This is because we want the max PER PLAYER - so we ask for the max and group by player.

Now that we've found the max for every player in Ligue 1 from 2022, lets calculate our sum to find 'Total Market Value in EUR for all players competing in Ligue 1 from 2022'. We'll do this by calling the sum() aggregate function on the results from our first query. we'll do this in the form of a subquery:

select
    sum(value)
from
    (select
        max(p.highest_market_value_in_eur) as value,
        p.player_id as player_id
    from
        Transfermarket.dbo.players$ p
    left join Transfermarket.dbo.player_valuations v on p.player_id = v.player_id
    inner join Transfermarket.dbo.competitions$ c on c.competition_id = v.player_club_domestic_competition_id
    where c.name = 'Ligue 1' and last_season = 2022
    group by p.player_id) max_per_player;

Hope this helps.