Having clause in SQL with example

Introduction to HAVING clause in SQL

The HAVING clause in SQL is used with the GROUP BY clause to filter groups based on a specified list of conditions. The following illustrates the HAVING clause syntax:

Syntax:

SELECT 
      column_name
FROM 
      table_name
GROUP BY 
      column_name
HAVING 
      Condition .....

Note: SQL processes the HAVING clause after the GROUP BY clause, you can not refer to the aggregate function in the select list by using its column alias name. The following query will through error:

SELECT 
      [Customer_ID], count ([Customer_ID]) AS ColCount
FROM 
      [dbo].[Orders_Tbl]
GROUP BY 
      [Customer_ID]
HAVING 
      (ColCount) > 25

Error:

SELECT  
     [Customer_ID], COUNT([Customer_ID]) AS ColCount
FROM 
     [dbo].[Orders_Tbl]
GROUP BY 
     [Customer_ID]
HAVING 
     COUNT ([Customer_ID]) > 25

Output look like below:

Having Clause Examples

Example #1:  HAVING clause in sql with AVG() function

Below query example will extract the result for  Order_ID whihc have average sales between 100 to 200.

SELECT 
      [Order_ID], Avg (Sales) AS ColAvg
FROM 
      [dbo].[Orders_Tbl]
GROUP BY 
      [Order_ID]
HAVING 
      Avg (Sales) BETWEEN 100 AND 200

Having clause in the above query extracting the average sales using Between operator to show sales between 100 to 200.

Below is the result:

Example #2: HAVING clause for total sales using SUM() and Avg() function

In below example we are trying to extract data for Customer_ID whihc have total sales more then 50,000 and average Order_Quantity is more then 20.

SELECT 
      [Customer_ID], Sum (Sales) AS ColSum , Avg([Order_Quantity]) AS ColAvg
FROM 
      [dbo].[Orders_Tbl]
GROUP BY 
      [Customer_ID]
HAVING 
      Sum (Sales) > 50000 AND Avg([Order_Quantity]) > 20

you need to put aggregare function SUM() and AvG() in having clause with AND operator to fulfill both conditions and it will show result as shown below

Result:

Example #3: Having clause in sql using MAX() function to show Maximum discount for customer

Below example shows the Customer_ID list for Maximum discount which is more then 10% percent, in below example percentage is in decimal place that is why in having clause condition you will se 0.10 number

SELECT 
      [Customer_ID], Max ([Discount]) AS ColMax 
FROM 
      [dbo].[Orders_Tbl]
GROUP BY 
      [Customer_ID]
HAVING 
      Max ([Discount]) > 0.10

Result:

Example #4: Orders have more then 20 quantity ordered using Count()

Below example shows the Customer_ID whihc have ordered the ordere quantity more then 20, using having Clause with Count() Function.

SELECT 
      [Customer_ID], Count ([Order_Quantity]) AS ColMax 
FROM 
      [dbo].[Orders_Tbl]
GROUP BY 
      [Customer_ID]
HAVING 
      Count ([Order_Quantity]) > 20

Result:

Example #5: Using Where clause and Having Clause

In below example, extracting data for Customer_ID which exist more then 4 using HAVING Clause and for those Customer_ID which have NOT NULL values using WHERE Clause

SELECT 
      [Order_ID], [Customer_ID], COUNT([Customer_ID]) as ColCount
FROM
      [dbo].[Orders_Tbl]
WHERE
      [Customer_ID] IS NOT NULL
GROUP BY 
      [Order_ID], [Customer_ID]
HAVING
      COUNT([Customer_ID]) > 4

Result:

Difference between Where and Having Clause in SQL :

More to understand SQL having and where clause below are the differences