SQL 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_IdOrderDateOrderPriceCustomer
12008/11/121000Hansen
22008/10/231600Nilsen
32008/09/02700Hansen
42008/09/03300Hansen
52008/08/302000Jensen
62008/10/04100Nilsen

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:
CustomerSUM(OrderPrice)
Nilsen1700

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:
CustomerSUM(OrderPrice)
Hansen2000
Jensen2000

No comments: