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

Question

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?

Answer 1

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

Answer 2

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