SQL HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

The HAVING clause is used to filter records that work on summarized GROUP BY results.

Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Example: HAVING with COUNT()

SELECT country, COUNT(*) as num_customers
FROM customers
GROUP BY country
HAVING COUNT(*) > 1;

Sample Table

Consider the following customers table:

| id | name         | country    |
|----|--------------|------------|
| 1  | John Doe     | USA        |
| 2  | Jane Smith   | USA        |
| 3  | Mike Johnson | Canada     |
| 4  | Emily Davis  | USA        |
| 5  | David Brown  | Canada     |

Result of HAVING

| country    | num_customers |
|------------|---------------|
| USA        | 3             |
| Canada     | 2             |

The result shows countries with more than one customer.

Example: HAVING with SUM()

SELECT country, SUM(order_amount) as total_sales
FROM orders
GROUP BY country
HAVING SUM(order_amount) > 300;

Sample Table

Consider the following orders table:

| id | customer_id | country    | order_amount |
|----|--------------|------------|--------------|
| 1  | 1            | USA        | 100          |
| 2  | 2            | USA        | 200          |
| 3  | 3            | Canada     | 150          |
| 4  | 4            | USA        | 300          |
| 5  | 5            | Canada     | 250          |

Result of HAVING

| country    | total_sales |
|------------|-------------|
| USA        | 600         |

The result shows countries with total sales greater than 300.

Example: HAVING with AVG()

SELECT country, AVG(order_amount) as avg_sales
FROM orders
GROUP BY country
HAVING AVG(order_amount) > 200;

Result of HAVING with AVG()

| country    | avg_sales |
|------------|-----------|
| USA        | 200       |
| Canada     | 200       |

The result shows countries with an average sales amount greater than 200.