How to Use HAVING Clause in SQL

How to Use HAVING Clause in SQL

6 mins read523 Views Comment
Vikram
Vikram Singh
Assistant Manager - Content
Updated on Jan 9, 2024 11:07 IST

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.

SQL HAVING

Table of Content

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 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_names
FROM table_name
WHERE conditions
GROUP BY column_name
HAVING conditions
ORDER BY column_name;
Copy code

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 Employee
GROUP BY Department;
Copy code

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 Employee
GROUP BY Department
HAVING SUM(Salary) >= 20;
Copy code

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 Student
WHERE Education <> ‘UG’
GROUP BY Department
HAVING SUM(Salary) >= 20;
Copy code

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 Employee
GROUP BY Department
HAVING SUM(Salary) >= 15
ORDER BY SUM(Salary) DESC;
Copy code

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

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.

About the Author
author-image
Vikram Singh
Assistant Manager - Content

Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio