SQL HAVING Clause

The SQL Having Clause is used to restrict the number of rows (or records) returned by the Group By Clause. Where Clause does not allow us to check any conditions against the aggregated data. So, to Check any conditions against the aggregated data we have to use the Having Clause. Please refer Group By Clause before looking into Having Clause.

TIP: To use Having Clause, we have to use Group By Clause because Having clause filters data that we get from Group By Clause. If we haven’t used GROUP BY clause then, Having behaves like a WHERE clause.

NOTE: Where Clause is applied to each row before the Group By Clause is implement but Having Clause is applied after the Group by Statement.

SQL Having Clause Syntax

The Having Clause along with Group By Statement in SQL Server can be written as:

-- SQL Server Having Clause Syntax
SELECT [Column1], [Column2]...[ColumnN],
       Aggregate Function (Expression | Column_Name) 
FROM [Source]
WHERE [Conditions] -- Optional
GROUP BY [Column1], [Column2]...[ColumnN]
HAVING [Conditions] -- This Condition is on Aggregate Function (Expression | Column_Name)

• Columns: It allows us to choose the number of columns from the tables. It may be One or more.
• Aggregate Functions: We can use any of the aggregate functions here. SUM, MIN, AVG, MAX and COUNT are the functions we can use.
• Source: One or more tables present in the Database. SQL JOINs are used to join multiple tables.
• Conditions: Here we have to provide the filters or conditions. If the condition is TRUE then only SELECT Statement will return the records.
• Group By: All the Selected Columns which are not part of the Aggregate Functions should place after this Group by clause.
• Having: We can provide the Filters or Conditions on the Aggregated Data we got from the Group By Clause.

In this article, we will show you, How to write the SQL Having Clause to filter the data after the aggregate function is applied by the group by clause. We are going to use the below-shown data to explain the Having Clause in SQL Server with an example.

SQL Having Sigle Condition Examples

SQL Having Sum Example

In this example, we are going to use a single condition in the Having Clause.

-- SQL Server Having Single Condition Example
SELECT [Occupation]
      ,Education
	  ,SUM([YearlyIncome]) AS [Max Income]
  FROM [Customer] 
  GROUP BY [Occupation], [Education]
  HAVING SUM([YearlyIncome]) > 60000

In the above query, Group By statement will Group the customers by Occupation and Education. Next, Having Clause will check the condition whether the SUM([Yearly Income]) > 60000.

OUTPUT

SQL Having Max Example

In this example, we are using the Max Function in the Having Clause. Below statement returns records whose Maximum yearly income is greater than or equal to 60000.

-- SQL Server Having Max Example
USE [SQL Tutorial]
GO
SELECT [Education]
      ,MAX([YearlyIncome]) AS [Max Income]
  FROM [Customer] 
  GROUP BY Education
  HAVING MAX([YearlyIncome]) >= 60000

OUTPUT

SQL Having Max Example 2

In this example, within the select statement, we are using Max function on Multiple columns.

-- SQL Server Having Max Example
SELECT [Education]
      ,MAX([YearlyIncome]) AS [Max Income]
	  ,MAX(Sales) AS [Max Sales]
  FROM [Customer] 
  GROUP BY Education
  HAVING MAX([YearlyIncome]) >= 60000

In the above query, Group By statement will Group the customers by Occupation and Education. Next, Having Clause will check the condition whether the group Maximum Yearly Income is greater than 60000. Select statement selects the Education, Maximum yearly Income, and Maximum Sales.

OUTPUT

SQL Having Max Example 3

The Select statement selects the Occupation, Education, Sum of yearly Income, and Maximum Sales. Next, Group By statement Group the customers by Occupation and Education.

Having Clause will check the condition whether the SUM([Yearly Income]) > 60000.

-- SQL Server Having Clause Example
SELECT [Occupation]
      ,Education
	  ,SUM([YearlyIncome]) AS [Sum Of Income]
	  ,MAX([YearlyIncome]) AS [Max Income]
  FROM [Customer] 
  GROUP BY [Occupation], [Education]
  HAVING SUM([YearlyIncome]) > 60000 

OUTPUT

SQL Having Multiple Conditions

In this example, we are going to use Multiple Conditions in the Having Clause.

In the below query, Group By statement will Group the customers by Occupation and Education. Next, Having Clause will check the condition whether the SUM([Yearly Income]) > 60000 and less than 200000.

-- SQL Server Having Multiple Conditions Example
SELECT [Occupation]
      ,Education
	  ,SUM([YearlyIncome]) AS [Max Income]
  FROM [Customer] 
  GROUP BY [Occupation], [Education]
  HAVING SUM([YearlyIncome]) > 60000 AND
	SUM([YearlyIncome]) < 200000

OUTPUT

SQL Having Order By Example

The SQL Server allows you to use Order By Clause along with the Group By.

The following query Sort the Data by Sum of Yearly Income in Descending Order.

-- SQL Server Having Order By Example
SELECT [Occupation]
      ,Education
	  ,SUM([YearlyIncome]) AS [Sum Of Income]
	  ,MAX([YearlyIncome]) AS [Max Income]
  FROM [Customer] 
  GROUP BY [Occupation], [Education]
  HAVING SUM([YearlyIncome]) > 60000 
  ORDER BY SUM([YearlyIncome])  DESC

OUTPUT

SQL Having Vs Where Clause

We can also use the Where Clause along with the Having Clause.

The following query will first exact the customer’s data whose Education is Not equal to [Partial High School]. Next, Group By statement group the customers by Occupation and Education. Last, Having Clause will check the condition whether the SUM([Yearly Income]) > 60000 or not.

-- SQL Server Having vs Where Example
SELECT [Occupation]
      ,Education
	  ,SUM([YearlyIncome]) AS [Sum Of Income]
	  ,MAX([YearlyIncome]) AS [Max Income]
  FROM [Customer] 
  WHERE Education <> 'Partial High School'
  GROUP BY [Occupation], [Education]
  HAVING SUM([YearlyIncome]) > 60000 

OUTPUT

To explain the same, we are commenting Having Clause. Now you can all the records whose Education is not Partial High School

-- SQL Server Having vs Where Example
SELECT [Occupation]
      ,Education
	  ,SUM([YearlyIncome]) AS [Sum Of Income]
	  ,MAX([YearlyIncome]) AS [Max Income]
  FROM [Customer] 
  WHERE Education <> 'Partial High School'
  GROUP BY [Occupation], [Education]
  --HAVING SUM([YearlyIncome]) > 60000 

OUTPUT

This time we are commenting on the Where Clause. That is why you are seeing the Partial High School in the result window.

-- SQL Server Having vs Where Example
SELECT [Occupation]
      ,Education
	  ,SUM([YearlyIncome]) AS [Sum Of Income]
	  ,MAX([YearlyIncome]) AS [Max Income]
  FROM [Customer] 
  --WHERE Education <> 'Partial High School'
  GROUP BY [Occupation], [Education]
  HAVING SUM([YearlyIncome]) > 60000 

OUTPUT

Thank You for Visiting Our Blog