The SQL HAVING Clause Explained

What is the SQL HAVING clause? Why do you need it, and where do you use it? We’ll explain HAVING in detail.

HAVING is a very common clause in SQL queries. Like WHERE, it helps filter data; however, HAVING works in a different way. If you are familiar with the GROUP BY clause and have only heard about HAVING – or if you’re not familiar with HAVING at all – this article is what you need. Read on and extend your knowledge of HAVING in SQL!

What Is the SQL HAVING Clause?

In SQL, the HAVING clause:

• Filters data based on defined criteria.
• Is commonly used in creating reports.
• Is only used in SELECT.
• Works with GROUP BY.

If you know the GROUP BY clause, you know that it is used to aggregate values: it puts records into groups to calculate aggregation values (statistics) for them. HAVING filters records according to these aggregate values. We will thoroughly discuss that here, but you will find more details in our interactive course SQL Basics.

If you need a refresher on GROUP BY, I recommend the articles GROUP BY in SQL Explained by Kateryna Koidan and Grouping Data in SQL Server by Belma Mesihovic.

HAVING Syntax

Before we start with an example, let’s look at the syntax of the HAVING clause. HAVING is always placed after the WHERE and GROUP BY clauses but before the ORDER BY clause. Have a look:

SELECT column_list 
FROM table_name
WHERE where_conditions
GROUP BY column_list
HAVING having_conditions
ORDER BY order_expression

The HAVING clause specifies the condition or conditions for a group or an aggregation.

The employee table below helps us analyze the HAVING clause. It contains employee IDs (the emp_id column), the department where that employee works, and the employee’s salary.

To calculate the sum of salaries for each department, you’d write this query:

SELECT department, SUM(salary)
FROM employee
GROUP BY department;

Here’s the result:

Now, suppose that you need to display the departments where the sum of salaries is $50,000 or more. In this case, you should use a HAVING clause:

SELECT department, SUM(salary)
FROM employee
GROUP BY department
HAVING SUM(salary) >= 50000;

And the result is:

As you see, the result set contains only the sum of salaries for the HR and Finance departments. This is because the sum of Marketing salaries is below $50,000.

This query first groups records according to departments and computes aggregate values – in this case, the sum of all salaries. In the next step, the condition in HAVING is checked: we compare the value returned by SUM(salary) for a given department to $50,000. If this value is $50,000 or more, the record is returned. In our example, the summed salaries for the HR ($51,000) and Finance ($132,000) departments are shown.

Filtering Rows Using WHERE and HAVING

Next, let’s see how to filter rows at the record level and at the group level in the same query. First, look at the data in the report table sale:

The query below selects the sum of all sales for each salesperson whose average sale value is higher than $20,000. (Note: The salesperson with ID=3 is not included, as they only started working in March.)

SELECT salesman_id, SUM(total_value)  
FROM sale 
WHERE salesman_id != 3
GROUP BY salesman_id 
HAVING SUM(total_value) > 40000;

Here is the result:

This query first filters records, using the WHERE clause to select records with salesman ID other than 3 (WHERE salesman_id != 3). Next, it calculates the sum of total sales for sales reps with the IDs 1 and 2. It does this by individually grouping records for both reps (GROUP BY salesman_id). At the end, the query filters records by using HAVING to check if the aggregate value (sum of total sales) is over $40,000 (HAVING SUM(total_value) > 40000).

Filtering Rows on Multiple Values Using HAVING

The HAVING clause also allows you to filter rows using more than one aggregate value (i.e. values from different aggregate functions). Look at the next query:

SELECT salesman_id, SUM(total_value) 
FROM sale 
WHERE salesman_id != 3
GROUP BY salesman_id 
HAVING SUM(total_value) > 36000 AND AVG(total_value) > 15000;

The result:

This query returns the IDs of salespeople who 1) have total sales over $36,000, and 2) average over $15,000 in sales each month. Only the sales rep with ID=1 meets the two conditions. Notice that we didn’t select the average total sales for each salesperson, but only the sum of all their sales; the average is only in the HAVING condition.

The Difference Between HAVING and WHERE

The example from the last section showed how to filter records with both WHERE and HAVING. Now we will consider the difference between these two clauses.

The basic difference is that WHERE works on individual records and HAVING works on grouped records (after the GROUP BY is processed). HAVING is used only in SELECT statements, but WHERE can be used in other statements, like DELETE or UPDATE.

HAVING and WHERE filter data at different moments. WHERE is processed before GROUP BY. This means that first the records are selected and then filtered with WHERE. It is record-level filtering. After that, the result records are grouped and the aggregated value is calculated.

HAVING filters records at group level – after WHERE and GROUP BY. HAVING checks if the aggregate value for a group meets its condition(s). You should use an aggregate function to filter records only in HAVING; WHERE cannot include an aggregate function.

You can read more about the difference between WHERE and HAVING in HAVING vs. WHERE in SQL: What You Should Know by Ignacio L. Bisso.

HAVING: A Very Useful Clause

HAVING is very useful in SQL queries. It filters data after rows are grouped and values are aggregated – something you’ll often do in reports.

I hope this article has helped you understand the HAVING clause. Maybe it even moves you to extend your SQL knowledge. If you are interested in learning more about SQL, try our interactive SQL Basics course on the LearnSQL.com platform.