Yogesh Chauhan's Blog

Why do we need HAVING Clause in SQL?

in SQL/MySQL on September 5, 2019


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

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.

Most Read

#1 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #2 How to add Read More Read Less Button using JavaScript? #3 How to check if radio button is checked or not using JavaScript? #4 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to uninstall Cocoapods from the Mac OS?

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface
You might also like these
Some Useful Operators in The SQL WHERE ClauseSQL/MySQLHow to progressively load images and add a blurry placeholder?CSSHow to import a CSS file using PHP code and not HTML code?PHPEXISTS and NOT EXISTS in PostgresPostgresHow to create a dynamic countdown using HTML and JavaScript?HTMLWordPress: How to loop through a repeater field in ACF?WordPress