HAVING CLAUSE

Common Use Case
Like subqueries in the WHERE clause, subqueries in the HAVING clause can be used to implement dynamic filter criteria. A key difference is the latter operates on grouped rows. This type of subquery typically filters grouped rows by the results of an aggregate function (e.g., AVG, MIN, MAX, SUM, etc.).

Example Data
The schemas, corresponding tables, and data used in the example problems can be found at livesql.oracle.com. To more easily follow the examples covered on this page, consider first reading the table descriptions below and reviewing each table’s contents. To review the data maintained in each table, click on the icon next to the table name. To hide the data, click the icon again.

Real-world Examples
Explore the following two real-world examples to obtain a better understanding on how this type of subquery can be used.

Top Customer(s)
Often, companies wish to understand what their best customers look like in terms of the data attributes captured for each. In this example, those attributes are the customer’s date of birth, marital status, gender, and income level. With this in mind, consider the task of generating a report containing the customer information associated with those customers who have placed the largest number of orders. Retrieving the number of orders placed per customer is straightforward. However, retrieving only those customers with an order count value equal to the maximum order count for any single customer is more involved.

One way to achieve this is to use a subquery in the HAVING clause to first determine the value associated with the largest order count. Once this value is known, the outer query can be filtered to only those rows containing an order count value equal to the value returned by the subquery. By using the subquery, the hardcoding of values is avoided, and the query always returns the customers with the most orders placed based on the data available at runtime. For routine reports, this is a big win and less risky.

This strategy has been implemented in the query below. A subquery in the HAVING clause is used to first determine the value associated with the largest order count. This subquery also contains a subquery. The derived table, customer_order, returns the number of orders placed by each customer. Subsequently, the maximum order count value is computed across the contents of this derived table. Finally, the outer query is filtered to those customers having an order count value equal to the value returned by the subquery (i.e., 5). For clarity, see the corresponding result set following the query.

SELECT
    oe.customers.customer_id,
    oe.customers.date_of_birth,
    oe.customers.marital_status,
    oe.customers.gender,
    oe.customers.income_level,
    COUNT(oe.orders.order_id) AS order_count
FROM
    oe.customers
    INNER JOIN
    oe.orders
        ON oe.customers.customer_id = oe.orders.customer_id
GROUP BY
    oe.customers.customer_id,
    oe.customers.date_of_birth,
    oe.customers.marital_status,
    oe.customers.gender,
    oe.customers.income_level
HAVING
    COUNT(oe.orders.order_id) = (
        SELECT
        	MAX(customer_order.order_count)
        FROM (
        	SELECT
        		COUNT(oe.orders.order_id) AS order_count
            FROM
        		oe.orders
            GROUP BY
        		oe.orders.customer_id
        ) customer_order
	);

Below Average Performance
Imagine it’s the final days of year 2008 and you’re a manager over several sales representatives. A yearly task you perform is to evaluate your sales representatives’ performance. Part of the evaluation process is to understand which sales representatives are performing below average in terms of sales. Total sales numbers change every year and, as a result, so does the average for which representatives are compared against.

Is this a routine report? Yes. Do values change over time? Yes. Do you want to risk comparing your sales representatives against a value that is out of date or entirely inaccurate? No. Sounds like a job for a subquery.

In the query below, a subquery is used to gather the total sales per sales representative for those sales made in year 2008. See derived table, sales_rep. Subsequently, the average is computed across those values. This single value is returned by the subquery and compared against each grouped row’s (i.e., sales representative) total sales value. As a result, the grouped rows are filtered to those sales representatives with below average sales for the year. For clarity, see the corresponding result set following the query.

SELECT
	oe.orders.sales_rep_id,
    SUM(oe.orders.order_total) AS total_sales
FROM
	oe.orders
WHERE
    oe.orders.sales_rep_id IS NOT NULL
    AND oe.orders.order_date >= '2008-01-01'
    AND oe.orders.order_date < '2009-01-01'
GROUP BY
    oe.orders.sales_rep_id
HAVING
    SUM(oe.orders.order_total) < (
    	SELECT
    		AVG(sales_rep.order_total)
    	FROM (
    		SELECT
    			SUM(oe.orders.order_total) AS order_total
    		FROM
    			oe.orders
            WHERE
    			oe.orders.sales_rep_id IS NOT NULL
                AND oe.orders.order_date >= '2008-01-01'
                AND oe.orders.order_date < '2009-01-01'
    		GROUP BY
    			oe.orders.sales_rep_id
        ) sales_rep
    );

Knowledge Check
If you’d like to confirm your understanding on the use of subqueries in the HAVING clause and other fundamental concepts, consider solving a handful of my practice problems here.