Should a column that is being used in the HAVING clause always be
referenced in the GROUP BY clause even if that column is contained in
an aggregate function in the SELECT list (as  shownin the second
example below) unless the column being used in the HAVING clause is
contained in an aggregate function (as shown in the third example
below)?

For e.g. consider the following query:

----------------------------------------------
SELECT PID,Product,SUM(Price) AS TotalPrice
FROM Customers
GROUP BY PID
HAVING Price>100
ORDER BY PID
----------------------------------------------

The above query, when executed, generates an error saying

----------------------------------------------
Column 'Customers.Price' is invalid in the HAVING clause because it is
not contained in either an aggregate function or the GROUP BY clause.
----------------------------------------------

The error says that Price isn't contained in an aggregate function but
in the SELECT list, Price has been contained in the aggregate function
SUM! So why the error?

If the GROUP BY clause in the above query is slightly modified to this
(with the rest remaining as it is):

----------------------------------------------
SELECT PID,Product,SUM(Price) AS TotalPrice
FROM Customers
GROUP BY PID,Price  /*Price added to the GROUP BY clause*/
HAVING Price>100
ORDER BY PID
----------------------------------------------

then it works fine!

If the very first query is again modified slightly by including the
aggregate function SUM in the HAVING clause:

----------------------------------------------
SELECT PID,Product,SUM(Price) AS TotalPrice
FROM Customers
GROUP BY PID     /*No Price column in the GROUP BY clause; only PID*/
HAVING SUM(Price)>100  /*Using SUM in the HAVING clause*/
ORDER BY PID
----------------------------------------------

this also works fine (of course, the resultset won't be the same as the
resultset of the second query)!

Confusions galore!!!

Thanks,

Arpan

Using CASE in WHERE clause
Tempdb is growing and I can't tell what process is to blame
Beating a dead horse - Drop table from proc
Stored Procedure (Weird Question)
Sql Server Lock