WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

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;

Database

In this tutorial we will use the database.

Below is a selection from the "Orders" table:

OrderID CustomerID EmployeeID OrderDate ShipperID
10248 90 5 1994-02-11 3
10249 81 6 1999-01-06 1
10250 34 4 2018-04-08 2

And a selection from the "Employees" table:

EmployeeID LastName FirstName BirthDate Photo Notes
1 Maria R. Fern January 14, 1972 EmpID1.pic Education includes a BA....
2 Martin F. Thompson November 5, 1993 EmpID2.pic Andrew received his BTS....
3 Percy B. Campbell January 5, 1986 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;

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;