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 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
How to make HTML form interactive and using CSS?CSSWhat are Stored Procedures for SQL Server?SQL/MySQLQuery to increment or decrement value in MySQL ignoring negative valuesSQL/MySQLHow to add Google Map in WordPress using ACF?WordPressJavaScript String Properties and MethodsJavaScript12 URLSearchParams methods in JavaScriptJavaScript