SQL - Having Clause

SQL HAVING Clause

1. The Purpose of the SQL HAVING Clause

The SQL HAVING clause is used to filter query results based on aggregate functions. While the WHERE clause filters individual rows before any grouping occurs, the HAVING clause filters entire groups of rows after they have been aggregated by the GROUP BY clause. Therefore, the HAVING clause is almost always used in conjunction with GROUP BY.

You can use standard aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX() within the HAVING clause to specify conditions for the groups.

2. Differentiating HAVING from WHERE

This is a frequent point of confusion for newcomers. The key difference lies in the timing of the filtering process:

• The WHERE clause filters rows before aggregation. It operates on individual row data.
• The HAVING clause filters groups after aggregation. It operates on the results of aggregate functions.

Analogy: Imagine a restaurant. The WHERE clause is like a waiter discarding individual orders that don’t meet a criterion (e.g., ‘no onions’). The GROUP BY clause is like grouping all orders by table number. The HAVING clause is like the manager checking which tables (groups) have a total bill (an aggregate value) over $100.

3. Syntax and Logical Query Processing Order

The basic syntax for a query including the HAVING clause is as follows:

SELECT column_name(s), aggregate_function(column_name)
FROM table_name
WHERE condition(s) -- Optional: Filters rows before grouping
GROUP BY column_name(s)
HAVING condition(s) -- Mandatory for group filtering
ORDER BY column_name(s); -- Optional: Sorts the final result set

It’s crucial to understand the logical order in which a database processes a query, as this explains why HAVING works on aggregated data and WHERE does not:

1. FROM / JOIN
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT / OFFSET / FETCH

4. Practical Examples with Aggregate Functions

Let’s use a modern employees table for our examples. This table stores employee details, including their department and salary.

-- Setup: Creating and populating the 'employees' table
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL
);

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('Alice', 'Smith', 'Engineering', 95000.00),
('Bob', 'Johnson', 'Engineering', 110000.00),
('Charlie', 'Brown', 'HR', 60000.00),
('Diana', 'Prince', 'Marketing', 75000.00),
('Ethan', 'Hunt', 'Engineering', 125000.00),
('Fiona', 'Glenanne', 'Marketing', 82000.00),
('George', 'Costanza', 'Sales', 88000.00);

Using HAVING with COUNT()

Let’s find departments that have more than one employee.

-- Find departments with 2 or more employees
SELECT
    department,
    COUNT(id) AS number_of_employees
FROM employees
GROUP BY department
HAVING COUNT(id) >= 2;

Output

department	number_of_employees
Engineering	3
Marketing	2

Using HAVING with SUM() and AVG()

Now, let’s identify departments where the average salary exceeds $90,000.

-- Find departments with an average salary over $90,000
SELECT
    department,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 90000.00;

Output

department	average_salary
Engineering	110000.00

Using HAVING with MIN() and MAX()

This query finds departments where the maximum salary is at least $120,000.

-- Find departments where at least one employee earns $120,000 or more
SELECT
    department,
    MAX(salary) AS max_salary_in_dept
FROM employees
GROUP BY department
HAVING MAX(salary) >= 120000.00;

Output

department	max_salary_in_dept
Engineering	125000.00

5. Combining HAVING with ORDER BY

The ORDER BY clause is applied after the HAVING clause, allowing you to sort the final filtered groups. Let’s find all departments, calculate their total salary payroll, and then show only those with a total payroll over $150,000, sorted from highest payroll to lowest.

SELECT
    department,
    SUM(salary) AS total_payroll
FROM employees
GROUP BY department
HAVING SUM(salary) > 150000.00
ORDER BY total_payroll DESC;

Output

department	total_payroll
Engineering	330000.00

6. Common Mistakes and Best Practices

• Mistake: Using a non-aggregated column in HAVING that is not in GROUP BY. This will cause an error in most SQL databases.
• Mistake: Using aliases from the SELECT list in the HAVING clause. Some databases support this (like MySQL), but it’s not standard SQL and can fail elsewhere. The standard practice is to repeat the aggregate function.
• Best Practice: Filter as much as possible with WHERE before grouping. The WHERE clause reduces the number of rows that need to be processed by the GROUP BY and HAVING clauses, which is more efficient. For instance, if you only care about ‘Engineering’ salaries, filter WHERE department = 'Engineering' first.
• Best Practice: Keep the HAVING clause clean and focused on aggregate conditions. Complex logic can often be simplified using Common Table Expressions (CTEs).

7. Real-World Application Scenario

Imagine you are an analyst for an e-commerce platform. A common task is to identify high-value customers. You can use the HAVING clause to find customers who have placed more than 5 orders or whose total spending exceeds $1,000.

-- Pseudo-code for finding high-value customers
SELECT
    customer_id,
    COUNT(order_id) AS total_orders,
    SUM(order_value) AS total_spent
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 5 OR SUM(order_value) > 1000.00
ORDER BY total_spent DESC;