SQL HAVING Clause

The SQL Server Having Clause restricts the number of rows (or records) returned by the Group By. This article shows how to write the 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 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 this clause behaves like a WHERE.

SQL Server Having Clause Syntax

The Having Clause works along with Group By Statement written 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, and 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 this one.

Having Clause Single Condition Examples

Let me show you how to write with a single condition in it.

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 Server Group By is implemented. However, the Having is used after it.

Max Example

In this example, we use the Max Function in the 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, we are using the Max function on Multiple columns within the select statement.

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, the 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 Groups 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

Multiple Conditions

This example uses Multiple Conditions in it. First, the 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 Clause With Multiple Conditions 5

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

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 is used for grouping the customers by Occupation and Education. Last, the 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 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 see 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