Aggregates on the right side of an APPLY cannot reference columns from the left side.

Riley 380 Reputation points
2023-10-23T07:34:00.81+00:00

Trying to use aggregate functions on different group. I need to display max value of entire column and the average value of each group.

Here is the sample and query:

Declare @temp table (id int, groupid int,amount int);
insert into @temp values
(1,101,56),
(2,101,78),
(3,103,23),
(4,103,89),
(5,103,73)

select groupid,max_of_all,AVG(amount) as avg_of_group
from @temp cross apply(values(MAX(amount)))c(max_of_all)
group by groupid,max_of_all

Error message: Msg 4101, Level 15, State 1, Line 10

Aggregates on the right side of an APPLY cannot reference columns from the left side.

How to solve this?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,894 questions
{count} votes

Accepted answer
  1. CosmogHong-MSFT 23,716 Reputation points Microsoft Vendor
    2023-10-23T07:38:50.0566667+00:00

    Hi @Riley

    How about cross join, like this:

    select groupid,max_of_all,AVG(amount) as avg_of_group
    from @temp cross join(select MAX(amount) as max_of_all from @temp)a 
    group by groupid,max_of_all
    

    Best regards,

    Cosmog Hong

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 102.3K Reputation points
    2023-10-23T21:43:37.39+00:00

    If I understand this correctly:

    SELECT groupid, AVG(amount) as avg_of_group, MAX(MAX(amount)) OVER() AS max_of_all
    FROM   @temp
    GROUP  BY groupid
    
    
    
    0 comments No comments