sql_having

SQL HAVING Clause: Filtering Aggregated Results

The Role of the HAVING Clause

In SQL, the HAVING clause is used to filter groups created by the GROUP BY clause. It’s similar to the WHERE clause, but with a key difference: WHERE filters individual rows before they are grouped and aggregated, while HAVING filters entire groups after aggregation functions (like COUNT(), SUM(), AVG(), MIN(), MAX()) have been applied.

Why HAVING? Because WHERE Can’t See Aggregates

You cannot use aggregate functions directly in a WHERE clause. The WHERE clause operates on row-level data. By the time aggregate functions are computed, the WHERE clause filtering has already occurred. The HAVING clause was introduced specifically to address this need for filtering based on the results of aggregate functions.

SQL HAVING Syntax

The HAVING clause is typically used in conjunction with GROUP BY:

SELECT column_name(s), aggregate_function(column_name) AS alias_name
FROM table_name
WHERE condition -- Optional: filters rows before grouping
GROUP BY column_name(s)
HAVING aggregate_condition; -- Filters groups after aggregation

Order of Execution (Simplified):

1. FROM table_name
2. WHERE condition (filters rows)
3. GROUP BY column_name(s) (groups rows)
4. HAVING aggregate_condition (filters groups)
5. SELECT column_name(s), aggregate_function (selects data)
6. ORDER BY column_name(s) (sorts final results)

Demo Database Context

For our examples, imagine we’re working with a simplified version of the classic Northwind sample database. We’ll focus on two tables: Orders and Employees.

Sample Orders table structure and data:

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
... more orders ...

Sample Employees table structure and data:

EmployeeID | LastName  | FirstName | BirthDate
------------|-----------|-----------|-----------
1           | Davolio   | Nancy     | 1968-12-08
2           | Fuller    | Andrew    | 1952-02-19
3           | Leverling | Janet     | 1963-08-30
4           | Peacock   | Margaret  | 1958-09-19
5           | Buchanan  | Steven    | 1955-03-04
6           | Suyama    | Michael   | 1963-07-02
... more employees ...

SQL HAVING Examples

Example 1: Find employees who have registered more than 10 orders.

Here, we need to count orders per employee and then filter based on that count.

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

• INNER JOIN combines Orders and Employees.
• GROUP BY E.LastName groups the orders by employee last name.
• COUNT(O.OrderID) calculates the number of orders for each employee.
• HAVING COUNT(O.OrderID) > 10 filters these groups, keeping only those employees with more than 10 orders.
• Using table aliases (O for Orders, E for Employees) makes the query more readable.

Example 2: Find if employees ‘Davolio’ or ‘Fuller’ have registered more than 25 orders each.

This example combines a WHERE clause (to pre-filter for specific employees) and a HAVING clause.

SELECT
    E.LastName,
    COUNT(O.OrderID) AS NumberOfOrders
FROM Orders AS O
INNER JOIN Employees AS E ON O.EmployeeID = E.EmployeeID
WHERE E.LastName IN ('Davolio', 'Fuller') -- Filter rows before grouping
GROUP BY E.LastName
HAVING COUNT(O.OrderID) > 25; -- Filter groups after aggregation

• WHERE E.LastName IN ('Davolio', 'Fuller') first selects only orders handled by Davolio or Fuller.
• Then, GROUP BY E.LastName groups these filtered orders.
• Finally, HAVING COUNT(O.OrderID) > 25 checks if, among these pre-selected employees, any have more than 25 orders.

Common Learning Obstacles & Best Practices:

• Using WHERE for aggregate conditions: Remember, WHERE is for row-level filtering before GROUP BY. HAVING is for group-level filtering after GROUP BY.
• Using HAVING without GROUP BY: While some DBMS might allow HAVING without an explicit GROUP BY (treating the whole table as a single group), it’s standard practice and clearer to use HAVING with GROUP BY.
• Column aliases in HAVING: Standard SQL requires repeating the aggregate function in the HAVING clause (e.g., HAVING COUNT(O.OrderID) > 10). However, many modern DBMS (like MySQL, PostgreSQL) allow you to use the alias defined in the SELECT list (e.g., HAVING NumberOfOrders > 10). For maximum portability, repeat the function.
• Performance: If a condition can be applied before grouping (i.e., it doesn’t involve an aggregate function), it’s generally more efficient to use WHERE rather than HAVING.