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.

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

We hope that this EDUCBA information on “SQL HAVING Clause” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

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