The HAVING Clause
In this lesson, we will learn about the HAVING clause.
We'll cover the following
The HAVING clause
The HAVING clause is utilized in SQL as a conditional clause with the GROUP BY
clause. This conditional clause only returns rows where aggregate function results are matched with given conditions.
The HAVING
clause was added to SQL because the WHERE
keyword could not be used with aggregate functions.
Syntax
The basic syntax of the HAVING
clause is as follows:
SELECT column1, column2, ... columnN
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2, ... columnN
HAVING [ conditions ]
ORDER BY column1, column2, ... columnN;
As you can see, the HAVING
clause must follow the GROUP BY
clause in a query and must also precede the ORDER BY
clause if used.
Example
Consider the CUSTOMERS table below but with a few changes:
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Mark | 32 | Texas | 50,000 |
2 | Jeff | 23 | LA | 77,000 |
3 | John | 25 | NY | 65,000 |
4 | Emily | 23 | Ohio | 20,000 |
5 | John | 31 | Texas | 54,000 |
6 | Bill | 25 | Texas | 75,000 |
7 | Bob | 28 | NY | 31,000 |
8 | Elyse | 29 | Ohio | 43,000 |
9 | Tom | 27 | Washington | 35,000 |
10 | Jane | 22 | NY | 45,0000 |
As you can see, there are many customers that live at the same ADDRESS
(i.e. live in the same state).
We want to write a SQL statement that returns the number of customers in each state, but only if that state has more than 2 customers:
Get hands-on with 1200+ tech skills courses.