Wednesday, June 12, 2013

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

No comments:

Post a Comment