Jul 24, 2013

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a 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
)

INSERT tblPrice VALUES(1,'UK',30),(2,'UK',40) ,(1,'USA',50),(2,'USA',70)

Now if we will execute following sql query:

SELECT Location,SUM(price)
FROM tblPrice
WHERE SUM(price) > 100
GROUP BY Location

We will get error message like:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a 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 WHERE clause of a SELECT statement.

Solution:

We can write above query in alternative ways:

1.

SELECT Location,SUM(price)
FROM tblPrice
GROUP BY Location
HAVING SUM(price) > 100

2.

SELECT Location,SUM(price)
FROM tblPrice o
WHERE (SELECT SUM(i.price) FROM tblPrice i
     WHERE i.Location = o.Location
     GROUP BY i.Location ) > 100
GROUP BY Location

Output:

Location
(No column name)
USA
120.00

No comments:

Post a Comment