The
HAVING clause in SQL Server specifies that the SQL SELECT statement should only return rows where aggregate values meet the specified conditions.
It was added to the SQL language because the
WHERE keyword could not be used with aggregate functions. This clause operates only on a group of records
of a table and acts as a filter on it ie. we first use the the Group By clause to club the data together and
then we use the having clause to filter it.
SQL HAVING Clause SYNTAX
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
The 'Where' condition is not mandatory to be used in a 'Having' clause statement as shown in the example.
HAVING Clause Example
The
Products table has the following records:
| Prd001 |
Pen |
40.25 |
| Prd002 |
Pen |
100.75 |
| Prd003 |
Eraser |
10.35 |
| Prd004 |
Pencil |
20.45 |
| Prd005 |
Book |
50.65 |
| Prd006 |
Gum |
25.22 |
Now suppose based on the above table you want to calculate average of all the ProductPrice which is >= 50 then you can do so by using
the following command:
SELECT ProductName, SUM(ProductPrice)
FROM Products
GROUP BY ProductName
HAVING AVG(ProductPrice) >= 50
Note: All columns not featured in the aggregate functions has to be in the 'Group By' list.