The HAVING Clause The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. SQL HAVING Synt...
The HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.SQL HAVING 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 |
SQL HAVING Example
We have the following "Orders" table:| O_Id | OrderDate | OrderPrice | Customer |
|---|---|---|---|
| 1 | 2008/11/12 | 1000 | Hansen |
| 2 | 2008/10/23 | 1600 | Nilsen |
| 3 | 2008/09/02 | 700 | Hansen |
| 4 | 2008/09/03 | 300 | Hansen |
| 5 | 2008/08/30 | 2000 | Jensen |
| 6 | 2008/10/04 | 100 | Nilsen |
Now we want to find if any of the customers have a total order of less than 2000.
We use the following SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING SUM(OrderPrice)<2000 |
The result-set will look like this:
| Customer | SUM(OrderPrice) |
|---|---|
| Nilsen | 1700 |
Now we want to find if the customers "Hansen" or "Jensen" have a total order of more than 1500.
We add an ordinary WHERE clause to the SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders WHERE Customer='Hansen' OR Customer='Jensen' GROUP BY Customer HAVING SUM(OrderPrice)>1500 |
The result-set will look like this:
| Customer | SUM(OrderPrice) |
|---|---|
| Hansen | 2000 |
| Jensen | 2000 |
