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