SQL Having Clause

The SQL Having Clause is used to restrict the number of rows (or records) returned by the Group By. In this article, we show how to write the SQL Having Clause to filter the data after the group applies the aggregate function.

The Where 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 SQL Having Clause.

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

SQL Having Clause Syntax

The Sql Server Having Clause works along with Group By Statement write as:

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)

We have already explained the Columns, Aggregate Functions, Source, Conditions, Group By options. So, please refer to Group By before looking into this. Here, it helps to provide the Filters or Conditions on the Aggregated Data we got from the Group By.

We use the below data to explain the Having Clause in SQL Server.

SQL Having Clause Single Condition Examples

Let me show you the Having with a single condition in it.

SQL Having Sum Example

Use a single condition in the SQL Server Having sum function.

SELECT [Occupation]
      ,Education
   ,SUM([YearlyIncome]) AS [Max Income]
  FROM [Customer] 
  GROUP BY [Occupation], [Education]
  HAVING SUM([YearlyIncome]) > 60000

First, Group By will categorize the customers by Occupation and Education. Next, Having will check the condition whether the SUM([Yearly Income]) > 60000.

The Where Condition is applied to each row before the SQL Server GroupBy implemented. However, the Having is used after it.

Having Max Example

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

SELECT [Education]
      ,MAX([YearlyIncome]) AS [Max Income]
  FROM [Customer] 
  GROUP BY Education
  HAVING MAX([YearlyIncome]) >= 60000

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

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, SQL Having Clause will check the condition whether the group Maximum Yearly Income is greater than 60000. The Select statement selects the Education, Maximum yearly Income, and Maximum Sales.

SQL Server Having Clause Max Example 3

The Select statement selects the Occupation, Education, Sum of yearly Income, and Maximum Sales. Next, it Group the customers by Occupation and Education. It will check the condition whether the SUM([Yearly Income]) > 60000.

SELECT [Occupation]
      ,Education
   ,SUM([YearlyIncome]) AS [Sum Of Income]
   ,MAX([YearlyIncome]) AS [Max Income]
  FROM [Customer] 
  GROUP BY [Occupation], [Education]
  HAVING SUM([YearlyIncome]) > 60000 

SQL Server Having Multiple Conditions

This example uses Multiple Conditions in it. First, query will organize the customers by Occupation & Education. Next, it will check the condition whether the SUM([Yearly Income]) > 60000 and less than 200000.

SELECT [Occupation]
      ,Education
   ,SUM([YearlyIncome]) AS [Max Income]
  FROM [Customer] 
  GROUP BY [Occupation], [Education]
  HAVING SUM([YearlyIncome]) > 60000 AND
 SUM([YearlyIncome]) < 200000

SQL Server Having Order By Example

You can also use Order By along with it. The following query Sort the Data by Sum of Yearly Income in Descending Order.

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

SQL Having Vs Where

We can also use the Where conditions along with this one. This example shows Sql Server Having vs Where clause. The following query will first exact the customer’s data whose Education is Not equal to [Partial High School].

Next, Group By statement used for grouping the customers by Occupation and Education. Last, Having Clause will check the condition whether the SUM([Yearly Income]) > 60000 or not.

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 

To explain the difference, we are commenting on the last line of the SQL Having Clause query. Now you can see all the records whose Education is not Partial High School

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 

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

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