SQL HAVING Clause

SQL already had WHERE clause and we did just fine without Having Clause, for a while. But when we needed to aggregate functions, WHERE clause didn’t work. So, the main reason of adding HAVING clause to SQL was the inability of the WHERE when it comes to aggregate functions. 

Always Remember: WHERE clause doesn’t work with aggregate functions. 

Common aggregate functions are :

• Avg 
• Count
• Max
• Min
• Sum 

Some facts about HAVING Clause:

• It specifies a search condition for a group or an aggregate.
• It is usually used in a GROUP BY clause
• Even if you are not using GROUP BY clause, you can still use HAVING clause to any function same way as a WHERE clause.
• REMEMBER : You must use HAVING with SQL SELECT.

Let’s take a look at the syntax:

SELECT column(s)
FROM table 
WHERE condition
GROUP BY column(s)
HAVING condition
ORDER BY column(s);

As you can see in the syntax above, we can use both WHERE and HAVING clauses together as well. IN that case there will be 2 search conditions; one with WHERE clause and another one with Having Clause.

Let’s take a look at the example. 

SELECT COUNT(CustomerID), Country
FROM customers
GROUP BY Country
HAVING COUNT(CustomerID) > 1;

The query above will count customers from each country and then display the results with the country with more than 1 customers in it. You can see the results in the DEMO link provided at the end of the article. 

Click here to know more about GROUP BY

Let’s add sorting to the same example.

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 1
ORDER BY COUNT(CustomerID) DESC;

The query above will give us same results but with country with more customers will be on top.

Having with INNER JOIN

SELECT staff.FirstName, COUNT(orders.OrderID) AS NumberOfOrders
FROM (orders INNER JOIN staff ON orders.StaffID = staff.StaffID)
GROUP BY FirstName HAVING COUNT(orders.OrderID) > 1;

Let’s break down the query above. The INNER JOIN creates a result set in which there will be matching values in both staff and orders tables. Then we will use that result set to get FirstName and will apply COUNT on OrderID to get the number of orders. We are using GROUP BY on FirstName so the result set will count number of orders by the FirstName. AT the end, we are applying Having clause to remove orders less then 1.

Overall, the query will give us the FirstName of the staff who have placed more than 1 order. 

Having with IF

SELECT staff.FirstName, COUNT(orders.OrderID) AS NumberOfOrders
FROM (orders INNER JOIN staff ON orders.StaffID = staff.StaffID) WHERE FirstName = 'Charles' OR FirstName = 'Leman'
GROUP BY FirstName HAVING COUNT(orders.OrderID) > 1;

Let’s breakdown the query above. We are now checking if a specific person has placed more than 1 order.

We are mostly performing the same operations as the example before this one. The only difference is that we are just adding WHERE clause as well which checks for the matching data for the FirstName.

Share this post