YogeshChauhan . com

Why do we need HAVING Clause in SQL?

in SQL/MySQL on September 5, 2019

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.

amazon

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 How to Use SQL MAX() Function with Dates? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

Jun 16 What are Stored Procedures for SQL Server? Jun 16 What are Class Constants in PHP? Jun 15 A short basic guide on states in React Jun 15 How to define constants in PHP? Jun 15 How to define visibility for a property in PHP? Jun 15 How to use @if and @else in SCSS?

You might also like these

What Does Host-Based Intrusion Detection System (HIDS) Mean and What Are Some Advantages Over NIDS?MiscWhat are keys in React?ReactSQL Left JoinSQL/MySQLHow to add a ribbon on top of a container using CSS?CSSObservation of Human Behavior [Shopping Observation Example]MiscSelector Lists and Combinators in SCSS (Sass)SCSS