SQL Having Clause

[Arjun Reddy]

Hi All,

I was just working on SQL Having clause. I wrote the following query using having clause:

SELECT ORDER_NAME,SUM(PRICE) FROM ORDERS GROUP BY ORDER_NAME HAVING ORDER_NAME='TV';

Then I wrote the following query using where condition and not using the having clause:

SELECT ORDER_NAME,SUM(PRICE) FROM ORDERS WHERE ORDER_NAME='TV' GROUP BY ORDER_NAME;

Both are basically giving the same result. I am wondering now the usage of having clause because if it's usage is to filter the records that a GROUP BY returns, then it can be done using the where condition too right?

Thanks.

[Shailesh Chandra]

A HAVING clause restricts the results of a 'GROUP BY'.
The HAVING clause is applied to each group,A WHERE clause is applied to a select list.
Having clause can also filter your data on aggregate functions like, sum, avg, min etc.

Just try filter your query on aggregate function, then you will come to know about difference. for example try to filter on SUM(PRICE) >0

Shailesh
[ November 11, 2008: Message edited by: Shailesh Chandra ]

[Arjun Reddy]

Originally posted by Shailesh Chandra:



Just try filter your query on aggregate function, then you will come to know about difference. for example try to filter on SUM(PRICE) >0

Shailesh

[ November 11, 2008: Message edited by: Shailesh Chandra ]

Yes Shailesh, where clause does not seem to work on aggregate functions. Where as Having clause works perfectly fine.

Thanks.