YogeshChauhan.com
Why do we need HAVING Clause in SQL?
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.

dreamhost

Leave a Reply

Most Read

#1 How to set opacity or transparency using CSS? #2 Pagination in CSS with multiple examples #3 How to check if radio button is checked or not using JavaScript? #4 How to make HTML form interactive and using CSS? #5 How to uninstall Cocoapods from the Mac OS? #6 How to add Read More Read Less Button using JavaScript?



Recently Posted

Feb 24 How to modify the latest post array using get_posts() in WordPress? Feb 22 WordPress: How to get ACF field values from another post? Feb 22 WordPress: How to print ACF repeater field values? Feb 22 WordPress: How to print ACF array field values? Feb 21 WordPress: How to get field values in Advanced Custom Fields? Feb 21 WordPress: How to add a Search Icon in Menus with toggle effect using jQuery?



You might also like these

File System Integrity: How to Keep an Eye on Your Files and Folder Change?MiscA quick introduction to API, REST API and PostmanMiscCreate a responsive pricing table using simple HTML and CSSCSSWhere is the PHP log file located on Mac OS?PHPWhat is Git?MiscGive buttons accessible namesUI/UX