Having And Where Clause

Hi

   The SQL HAVING keyword provides a search condition for a group or aggregate. The SQL HAVING clause works together with the SQL SELECT clause. The SQL HAVING clause is somewhat similar to the SQL WHERE clause, because it specifies a search condition.

There is one important difference between SQL HAVING and SQL WHERE clauses. The SQL WHERE clause condition is tested against each and every row of data, while the SQL HAVING clause condition is tested against the groups and/or aggregates specified in the SQL GROUP BY clause and/or the SQL SELECT column list.

It is important to understand that if a SQL statement contains both SQL WHERE and SQL HAVING clauses the SQL WHERE clause is applied first, and the SQL HAVING clause is applied later to the groups and/or aggregates.

Example:-
SELECT City, AVG(AverageTemperature)
FROM Weather
GROUP BY City
HAVING AVG(AverageTemperature) > 19

Hi Michell,

Having Clause: Specifies a search condition for a group or an aggregate. HAVING is usually used with the GROUP BY clause.

For Example.
SELECT [Emp ID], COUNT(*) AS Total
 FROM [Employee Details]
GROUP BY [Emp ID]
HAVING (COUNT(*) > 50);

It returns all emp Id having more than 50 records in each.

we can't use HAVING unless  use GROUP BY its recommended.

Suppose I want the same information,
but I don't care about EmpId where nobody have any count. Since the total count by Empidis an aggregate figure,
 i.e., the figure is generated from a group of records, you must use HAVING to select the proper data.

Where Clause: Specifies a search condition to restrict the rows returned.

For Example.
SELECT [Emp ID], COUNT(*) AS Total
FROM [Employee Details]
WHERE  (COUNT(*) > 50)
GROUP BY [Emp ID] ;

 If a condition refers to an aggregate function,
put that condition in the HAVING clause. Otherwise, use the WHERE clause.

Thanks.

Hi Michell,

here is accepted answer for your quetion.........

http://www.c-sharpcorner.com/Forums/Thread/140769/what-i-s-the-major-difference-between-having-and-where.aspx

plz go through it.

Thank You,
Pravin.

Hi Michell,

HAVING clause: -Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query.

 

WHERE Clause:- is applied to each row before they are part of the GROUP BY function in a query.


Thanks

Hi Michell,

A HAVING specifies a search condition for a group or an aggregate function used in SELECT statement. A HAVING clause be used only with the SELECT statement. When GROUP BY is not used, HAVING behaves like a WHERE clause.

example:
SELECT city, COUNT(*)
FROM TestTable
GROUP BY city
HAVING city IN ('NY', 'NJ')
ORDER BY city


Please do not forget to mark "Accepted Answer".