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
|