SQL HAVING Clause

How HAVING Clause Works?

The GROUP BY clause always accompanies the HAVING clause. The GROUP BY clause groups together the data that match a certain criterion. It has three phases – split, apply, and combine. The split-phase divides the rows into groups. The apply phase applies some aggregate functions on the groups of data. The combined phase produces a single result by combining the groups with the aggregate function result.

Now that the groups are formed, the HAVING clause comes into the picture. The HAVING clause then filters out the groups which do not satisfy the given condition.

All in One Software Development Bundle(600+ Courses, 50+ projects)

Price
View Courses

600+ Online Courses | 50+ projects | 3000+ Hours | Verifiable Certificates | Lifetime Access
4.6 (86,697 ratings)

Code:

SELECT Col_A, avg(Col_B) as Col_B
FROM MyTable
GROUP BY Col_A
HAVING avg(Col_B)>30

Thus, in the example above, we see that the table is first to split into three groups based on the column Col­_A. The aggregate function to calculate the average of Col­_B values is then applied to the groups. This results in a single row for each group. The rows are then combined and filtered based on the condition in the HAVING clause.

Example

Now let us look at a real-world example.

Consider we have the following table of customers and the orders that they have placed with us.

Now, we want to know customers of which countries have placed a combined total of 5 or more orders with us. It could be a single customer placing more than 5 orders or 5 customers placing 1 order each.

To accomplish this, we would need to:

Step 1: Join the two tables.

Step 2: Group the customers based on their countries.

Step 3: Count the number of orders for each group.

Step 4: Filter the results for 5 or more orders.

Let’s formulate the command:

Code:

SELECT C.Country, COUNT(O.OrderId) as NumberOfOrders         -- Step 1,3
FROM Customers C                                                -- Step 1
INNER JOIN Orders O on C.CustomerID = O.CustomerID              -- Step 1
GROUP BY C.Country                                              -- Step 2
HAVING COUNT(O.OrderId) >= 5                                    -- Step 4
ORDER BY COUNT(O.OrderId) DESC

Here are the results:

Conclusion – SQL HAVING Clause

Thus, we have seen what the purpose of the HAVING clause is and how does it work. It is important to understand the basic working, or else you may end up getting confused about why the HAVING clause is not producing the desired results. Keep playing with various tables and joins and combinations along with the HAVING clause.

Recommended Articles

This is a guide to SQL HAVING Clause. Here we discuss working of the HAVING clause in SQL and the example with the following table of customers. You can also go through our other suggested articles –

1. SQL Insert Query
2. Foreign Key in SQL
3. Distinct Keyword in SQL
4. SQL Views