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;

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Orders" table:

OrderID CustomerID EmployeeID OrderDate ShipperID
10248 90 5 1996-07-04 3
10249 81 6 1996-07-05 1
10250 34 4 1996-07-08 2

And a selection from the "Employees" table:

EmployeeID LastName FirstName BirthDate Photo Notes
1 Davolio Nancy 1968-12-08 EmpID1.pic Education includes a BA....
2 Fuller Andrew 1952-02-19 EmpID2.pic Andrew received his BTS....
3 Leverling Janet 1963-08-30 EmpID3.pic Janet has a BS degree....

SQL HAVING Example

Now we want to find  if any of the employees has registered more than 10 orders.

We use the following SQL statement:

Example

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

Try it yourself »

Now we want to find if the employees "Davolio" or "Fuller" have registered more than 25 orders.

We add an ordinary WHERE clause to the SQL statement:

Example

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
WHERE LastName='Davolio' OR LastName='Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;

Try it yourself »

Color Picker

colorpicker
REPORT ERROR
HOME
PRINT
FORUM
ABOUT

Top 10 Tutorials

HTML Tutorial
CSS Tutorial
JavaScript Tutorial
XML Tutorial
SQL Tutorial
PHP Tutorial
ASP.NET Tutorial
jQuery Tutorial
Angular Tutorial
Bootstrap Tutorial

Top 10 References

HTML Reference
CSS Reference
JavaScript Reference
Browser Statistics
HTML DOM
XML DOM
PHP Reference
jQuery Reference
HTML Colors
HTML Character Sets

Top 10 Examples

HTML Examples
CSS Examples
JavaScript Examples
HTML DOM Examples
PHP Examples
jQuery Examples
XML Examples
XML DOM Examples
ASP Examples
SVG Examples

Web Certificates

HTML Certificate
HTML5 Certificate
CSS Certificate
JavaScript Certificate
jQuery Certificate
PHP Certificate
XML Certificate

Your Suggestion:

Thank You For Helping Us!

Your message has been sent to W3Schools.

W3Schools is optimized for learning, testing, and training. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using this site, you agree to have read and accepted our terms of use, cookie and privacy policy. Copyright 1999-2015 by Refsnes Data. All Rights Reserved.