An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference.

Suppose we have created a table tblPrice and inserted some data into in it in sql server:

CREATE TABLE tblPrice(

     ItemID INT,

     Location VARCHAR(50),

     Price MONEY

)

CREATE TABLE tblCountryLimit(

     CountrtName VARCHAR(50),

     LimitPrice MONEY

)

INSERT tblPrice VALUES

(1,'UK',30),

(2,'UK',40) ,

(1,'USA',50),

(2,'USA',70)

INSERT tblCountryLimit VALUES('UK',50),('USA',150)

Now if we will execute following sql query:

SELECT Location,SUM(price)

FROM tblPrice p INNER JOIN tblCountryLimit cl

ON p.Location = cl.CountrtName AND SUM(price) <= LimitPrice

GROUP BY Location

We will get error message like:

An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference.

Cause: We cannot use aggregate functions like COUNT, SUM, MAX, MIN, AVG etc directly in ON clause of a SELECT statement.

Solution:

We can write above query in alternative way:

SELECT Location,SUM(price) AS Price

FROM tblPrice p INNER JOIN tblCountryLimit cl

ON p.Location = cl.CountrtName

GROUP BY Location,LimitPrice

HAVING SUM(price) <= LimitPrice

Output:

Location	Price
USA	120.00