Back to articles list July 31, 2020 - 5 minutes read The SQL HAVING Clause Explained Dorota Wdzięczna Dorota is an IT engineer and works as a Data Science Writer for Vertabelo. She has experience as a Java programmer, webmaster, teacher, lecturer, IT specialist, and coordinator of IT systems. In her free time, she loves working in the garden, taking photos of nature, especially macro photos of insects, and visiting beautiful locations in Poland. Tags: sql learn sql GROUP BY 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 Getting the Hang of the GROUP BY Clause by Marian Dziubak 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. employee_iddepartmentsalary 1HR23000 2HR28000 3Finance35000 4Marketing15000 5Marketing25000 6Finance56000 7Finance41000 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: departmentsalary HR51000 Marketing40000 Finance132000 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: departmentsalary HR51000 Finance132000 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: salesman_idsale_monthtotal_value 1January34000 1February14000 1March22000 1April2000 2January20000 2February0 2March17000 2April0 3March1000 3April35000 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: salesman_idsum 172000 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: salesman_idsum 172000 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. Tags: sql learn sql GROUP BY You may also like GROUP BY Clause: How Well Do You Know It? The GROUP BY clause is the most basic way to compute statistics in SQL. It can be quite tough for beginners but it is really powerful. Read more Difference between GROUP BY and ORDER BY in Simple Words For someone who's learning SQL, one of the most common concepts that they get stuck with is the difference between GROUP BY and ORDER BY. Read more SQL Window Functions vs. GROUP BY: What’s the Difference? Window functions and GROUP BY may seem similar at first, but they’re quite different. Learn how window functions differ from GROUP BY and aggregate functions. Read more NULL Values and the GROUP BY Clause We've already covered how to use the GROUP BY clause but how does SQL's GROUP BY clause work when NULL values are involved? Find out! Read more Getting the Hang of the GROUP BY Clause GROUP BY is an important part of the SQL SELECT statement. But new SQL coders can run into some problems when this clause is used incorrectly. Here’s how to avoid those issues. Read more What Is the Difference Between a GROUP BY and a PARTITION BY? What is the difference between a GROUP BY and a PARTITION BY in SQL queries? When should you use which? You can find the answers in today's article. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.