HAVING CLAUSE

The HAVING clause is used to filter data to grouped rows that meet a condition or set of conditions. Conveniently, this clause makes use of the same comparison and logical operators previously discussed when exploring how to implement filtering criteria in the WHERE clause. Filters applied in the HAVING clause are typically based on the results of aggregate functions applied to pre-existing attributes, aggregate functions applied to derived attributes, the results of a subquery, or a combination of those. I will walk through each of these scenarios, starting with the use of pre-existing attributes.

Before reading further, consider reviewing the Example Data section below to familiarize yourself with the data being used throughout the examples on this page. If you are already familiar with the data, then skip to the Pre-existing Attributes section.

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.

Pre-existing Attributes
While discussing the GROUP BY clause, we explored a hypothetical scenario where the goal was to better understand the salaries of employees and their corresponding jobs. In that example’s query output, all grouped rows (i.e., job IDs) along with several computed measures were displayed. Let us build onto that example. Suppose there is a company-wide initiative to begin reducing the salary range of those employees within each job. Further suppose the company has decided, based on analyses conducted by the human resource department, the company should start off by targeting those jobs where the salary range is currently greater than $3000. The HAVING clause can be used to identify those jobs. In the code below, the difference between the maximum salary and minimum salary is computed for each group of employees having the same job. Each of the resulting values are then compared to the value of 3000. If the difference is greater than 3000, then the condition evaluates to TRUE for the grouped row or job ID value and is maintained in the output; otherwise, it is discarded. For clarity, review the code’s output below and compare that to the output originally shown while solving for the first real-world problem on my page dedicated to exploring the GROUP BY clause.

SELECT
    hr.employees.job_id,
    SUM(hr.employees.salary) AS total_salary,
    COUNT(hr.employees.employee_id) AS employee_count,
    AVG(hr.employees.salary) AS average_salary,
    MAX(hr.employees.salary) AS maximum_salary,
    MIN(hr.employees.salary) AS minimum_salary,
    MAX(hr.employees.salary) - MIN(hr.employees.salary) AS salary_range
FROM
    hr.employees
GROUP BY
    hr.employees.job_id;
HAVING
	MAX(hr.employees.salary) - MIN(hr.employees.salary) > 3000;

It is important to note the condition used in the HAVING clause above cannot and should not be placed in the WHERE clause. Why? The condition being evaluated requires the maximum salary and minimum salary to be computed for each job using the corresponding individual employee salary values. In other words, the grouping of rows must be performed, and the results of each calculation known prior to the filtering condition being evaluated. Because the WHERE clause operates on ungrouped rows, the necessary information is not known at the time the WHERE clause is processed.

Derived Attributes
In the previous example, the filtering condition in the HAVING clause made use of the pre-existing attribute, salary. In instances where the filtering criteria cannot be implemented based on pre-existing attributes, alone, values can be derived. For example, suppose a web development team is attempting to create customer-facing product category pages. The plan is to create one page per product category, displaying images of the products along with the corresponding descriptions. Each page can allocate a maximum of four thousand characters to product descriptions. This means regardless of the number of products within a category, the total number of characters available to describe all products within a single category is four thousand. For any product category exceeding this limit, the product category owner needs to work with the individual product owners to reduce the length of product descriptions. The task assigned to you is to simply generate a report containing the product categories exceeding the character limit. The report needs to contain the category ID, total number of characters dedicated to describing products, and the character reduction required to meet the limit.

To fulfill this task, the products can be grouped by their category ID values. Within each category, the total number of characters allocated to the category’s product descriptions can be computed in two steps. First, compute the length of each description using Oracle’s LENGTH function. Second, sum the individual lengths. Each sum represents the total number of characters used for all product descriptions within the specific product category. The same computation less four thousand can be used to determine the character reduction required. For clarity, see the code and corresponding output below.

SELECT
	oe.product_information.category_id,
    SUM(LENGTH(oe.product_information.product_description)) AS character_count,
    SUM(LENGTH(oe.product_information.product_description)) - 4000 AS character_reduction
FROM
    oe.product_information
GROUP BY
    oe.product_information.category_id
HAVING
    SUM(LENGTH(oe.product_information.product_description)) > 4000;

Subqueries
Thus far, the solutions to the real-world problems covered have used a static value for the operand on the right-hand side of a comparison operator. This is perfectly fine in instances where the comparison value is known in advance and does not require periodic updates; however, if the value needs to reflect the current state of something or represent the answer to a question at any given time, then it is not a viable, long-term solution. Instead, a subquery should be used to generate the value at the time the query is executed. For example, suppose the management team over the organization’s sales representatives wishes to monitor and award those sales representatives generating at least ten percent of the total sales (i.e., order totals) generated by all sales representatives. To generate a list of those sales representatives meeting the criteria at any given time, the raw value representing ten percent of total sales generated by sales representatives must be computed. It is important to note this value changes over time as more orders are placed by customers assisted by a sales representative. To account for the value’s dynamic nature, a subquery can be used to generate it.

In the code below, the operand on the right-hand side of the comparison operator in the HAVING clause is a subquery returning the value of interest. How does it work? All order total values within the orders table associated with a sales representative (i.e., the sales rep ID value is populated) are summated. That value is then multiplied by 0.10 and a single value is returned. The results of summating each sales representative’s order totals in the outer query are then compared to that value. Those sales representatives with a total order total greater than or equal to the value returned by the subquery satisfy the condition in the HAVING clause. Consequently, those sales representatives are returned in the output along with their order count and total order total values.

SELECT
	oe.orders.sales_rep_id,
    COUNT(oe.orders.order_id) AS order_count,
    SUM(oe.orders.order_total) AS total_order_total
FROM
	oe.orders
WHERE
	oe.orders.sales_rep_id IS NOT NULL
GROUP BY
	oe.orders.sales_rep_id
HAVING
	SUM(oe.orders.order_total) >= (
    	SELECT
        	SUM(oe.orders.order_total) * 0.10
        FROM
            oe.orders
        WHERE
            oe.orders.sales_rep_id IS NOT NULL
    );

Keep in mind, the example above is using a fictional data set. In practice and with a more complete and real data set, the solution will likely warrant a condition in the WHERE clause limiting the data to orders placed within a specific time period (e.g., month, year, etc.). The solution above incorporates a condition evaluating whether each sales representative’s all-time sales values meet the criteria. Additionally, management way wish to track the date(s) a specific sales representative’s total order total value accounted for at least ten percent of total sales as of the corresponding date and for the specified period.

Do not be concerned if the concept of a subquery or the entirety of the final example above isn’t quite clicking for you yet. At this point, a solid understanding of the first two problems and corresponding solutions is more than sufficient. In the long-term, you will need to understand how to use subqueries to assist you in solving complex problems. After acquiring a firm understanding of the BIG SIX, consider reading my pages dedicated to subqueries here. These pages explore the clauses for which subqueries can exist and walk through how to implement them to solve real-world problems.

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