SQL HAVING Clause

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 "Purchases" table:

PU_ID	OrderDate	OrderPrice	Customer
1	2015/11/12	1000	Karmen
2	2015/10/23	1600	Tendulkar
3	2015/09/02	700	Karmen
4	2015/09/03	300	Karmen
5	2015/08/30	2000	Jensen
6	2015/10/04	100	Tendulkar

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 Purchases
GROUP BY Customer
HAVING SUM(OrderPrice)<2000

The result-set will look like this:

Customer	SUM(OrderPrice)
Tendulkar	1700

Now we want to find if the customers "Karmen" 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 Purchases
WHERE Customer='Karmen' OR Customer='Jensen'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500

The result-set will look like this:

Customer	SUM(OrderPrice)
Karmen	2000
Jensen	2000

« Previous Chapter

Next Chapter »

Web Development

Java Tutorials

C Tutorials

Browser Scripting

Server Scripting

XML Tutorials