How to Use HAVING Clause in SQL
When working with a large dataset, a query may produce a large number of records. To filter out specific records, we can use the WHERE clause in SQL. However, the WHERE clause has some limitations. In this article, we will discuss the HAVING clause, how to use it, and the difference between HAVING and WHERE clause.
Table of Content
- What is the HAVING clause?
- HAVING with WHERE
- HAVING with ORDER BY
- Difference between HAVING and WHERE clause
What is the HAVING clause?
The HAVING clause in SQL is similar to the WHERE clause that is used to filter the data but in a different way.
- HAVING clause is used to filter the result obtained by the GROUP BY clause
- It is used with the aggregation function
- It can include one or more conditions
- The order of execution of the HAVING clause is after the GROUP BY clause and before the ORDER BY clause.
- It can only be used with the SQL SELECT clause
Syntax
SELECT column_namesFROM table_nameWHERE conditionsGROUP BY column_nameHAVING conditionsORDER BY column_name;
Must Read: Introduction to SQL
Must Read: SQL ACID Properties
Let’s take some examples to understand how to use the HAVING clause in SQL.
Here is the dataset (Employee) that contains the record Employee ID, Name, Department, Education, and their salary in Lacs.
| Employee ID | Name | Gender | Department | Education | Month of Joining | Salary |
| 1001 | Ajay | M | Engineering | Doctoral | January | 25 |
| 1002 | Babloo | M | Engineering | UG | February | 23 |
| 1003 | Chhavi | F | HR | PG | March | 15 |
| 1004 | Dheeraj | M | HR | UG | January | 12 |
| 1005 | Evina | F | Marketing | UG | March | 16 |
| 1006 | Fredy | M | Sales | UG | December | 10 |
| 1007 | Garima | F | Sales | PG | March | 10 |
| 1008 | Hans | M | Admin | PG | November | 8 |
| 1009 | Ivanka | F | Admin | Intermediate | April | 7 |
| 1010 | Jai | M | Peon | High School | December | 4 |
Must Read: What is the difference between SQL and MySQL?
Must Read: Difference between SQL and NoSQL
Example – 1 (i): Calculate the sum of salaries of each department.
Query
SELECT Department, SUM(Salary)FROM EmployeeGROUP BY Department;
Output
| Department | Salary |
| Engineering | 48 |
| HR | 27 |
| Marketing | 16 |
| Sales | 20 |
| Admin | 15 |
| Peon | 4 |
Must Read: Subqueries in SQL
Must Read: SQL CREATE TABLE
Example – 1 (ii): Find the departments in which the SUM of the salaries is greater than or equal to 20 lacs
Query
SELECT Department, SUM(Salary)FROM EmployeeGROUP BY DepartmentHAVING SUM(Salary) >= 20;
Output
| Department | Salary |
| Engineering | 48 |
| HR | 27 |
| Sales | 20 |
Must Read: SQL WHERE
Must Read: Introduction to Normalization
HAVING with WHERE
Example – 2: Find the department in which SUM salary is greater than or equal to 20 lacs, but the education of employees is not UG.
Query
SELECT Department, SUM(Salary)FROM StudentWHERE Education <> ‘UG’GROUP BY DepartmentHAVING SUM(Salary) >= 20;
Firstly, the above query will filter the record using the WHERE clause to select the record of the employees who are not undergraduates. Then it will calculate the SUM(Salary) of each department. finally, using the HAVING clause, the query will filter out those departments that are SUM(Salary) >= 20.
Output
| Department | Salary |
| Engineering | 25 |
Must Read: SQL LIMITS
Must Check: SQL Online Course and Certifications
HAVING with ORDER BY
Example – 3: Find the departments in which the SUM of the salaries is greater than or equal to 15 lacs and arrange the Salary in descending order.
Query
SELECT Department, SUM(Salary)FROM EmployeeGROUP BY DepartmentHAVING SUM(Salary) >= 15ORDER BY SUM(Salary) DESC;
Output
| Department | Salary |
| Engineering | 48 |
| HR | 27 |
| Sales | 20 |
| Marketing | 16 |
| Admin | 15 |
Must Read: SQL Comparison and Arithmetic Operator
Must Read: SQL Logical Operator
Best-suited Database and SQL courses for you
Learn Database and SQL with these high-rated online courses
Difference between HAVING and WHERE clause
| Parameter | WHERE Clause | HAVING Clause |
|---|---|---|
| Usage | Used to filter records before any groupings are made. | Used to filter values after they have been grouped. |
| Operational Stage | Operates on individual rows. | Operates on grouped records created by the GROUP BY clause. |
| Aggregate Functions | Cannot use aggregate functions (like SUM, AVG, COUNT, etc.). | It can use aggregate functions. |
| Applicability | Applied to SELECT, UPDATE, and DELETE statements. | Only applied with the GROUP BY clause in a SELECT statement. |
| Grouping Dependency | Independent of GROUP BY clause. | Directly dependent on the GROUP BY clause. |
Must Read: SQL Joins
Must Read: Window Function in SQL
Conclusion
This article has discussed various examples of using the HAVING clause in SQL. We have also covered how the HAVING clause is used with the WHERE and ORDER BY clauses. Finally, we talked about the difference between the WHERE and HAVING clauses. This article will be helpful for your Data Science and Data Analysis journey. Keep learning and sharing!
FAQs on SQL HAVING Clause
What is the SQL HAVING Clause?
The SQL HAVING clause is used to specify conditions for groups or aggregations in SQL queries, particularly after the data has been grouped by the GROUP BY clause. It's like a WHERE clause but for groups rather than individual records.
Can the HAVING Clause be Used Without the GROUP BY Clause?
The HAVING clause is typically used in conjunction with the GROUP BY clause. If used, it must follow the GROUP BY clause in a query and precede the ORDER BY clause. HAVING without GROUP BY is less common and behaves similarly to the WHERE clause.
How Do I Use the HAVING Clause to Filter the Sum of Values in a Group?
To filter groups based on the sum of a particular column, you can use the HAVING clause with the SUM function. For example, to find departments where the sum of salaries is more than a specific amount, you would use HAVING SUM(salary) > [amount].
Can I Use Aggregate Functions with the HAVING Clause?
Yes, the HAVING clause is specifically designed to work with aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX(). For instance, you can list all products with more than a certain number of orders using HAVING COUNT(ProductID) > [number].
How is the HAVING Clause Different from the WHERE Clause?
The WHERE clause filters individual records before the GROUP BY operation, while the HAVING clause filters groups of records after the GROUP BY operation. WHERE cannot use aggregate functions, but HAVING can.
Can I Combine WHERE and HAVING Clauses in a Single Query?
Yes, you can use both WHERE and HAVING in the same query. WHERE will filter individual records before they are grouped, and HAVING will filter these groups based on aggregate conditions.
Can the HAVING Clause Use Conditions Based on Multiple Aggregate Values?
The HAVING clause allows for filtering based on multiple aggregate values. For example, you can filter groups based on both the sum and average of a column, like HAVING SUM(column) > [value1] AND AVG(column) > [value2].
How Do I Use the HAVING Clause with the AVG Function?
To use the HAVING clause with the AVG function, you group your data with GROUP BY and then apply the HAVING condition based on the AVG function. For instance, to find products with an average quantity ordered above a certain threshold, you would use HAVING AVG(Quantity) > [threshold].
What Are Some Common Uses of the HAVING Clause?
Common uses include filtering groups to find the top n items, identifying outliers, and summarizing data for reporting purposes, like finding departments with total salaries within a specific range.
Can HAVING be Used for Complex Conditions?
Yes, the HAVING clause can be used for complex conditions involving multiple aggregate functions and logical operators, allowing for sophisticated data analysis and reporting.
Top Picks & New Arrivals
