The SQL Having Clause is used to restrict the number of rows (or records) returned by the Group By Clause. In this article, we show how to write the SQL Having Clause to filter the data after the group applies the aggregate function by clause. The 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 SQL Having Clause.
To use Sql Server 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, then the Having behaves like a WHERE clause.
SQL Having Clause Syntax
The Sql Server Having Clause along with Group By Statement in SQL Server write 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)
We have already explained the Columns, Aggregate Functions, Source, Conditions, Group By options. So, please refer to Group By Clause before looking into Having Clause. Here, Having helps to provide the Filters or Conditions on the Aggregated Data we got from the Group By Clause. We use the below data to explain the Having Clause in SQL Server.
SQL Having Single Condition Examples
Let me show you the Having clause with a single condition in it
SQL Having Sum Example
Use a single condition in the SQL Server 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
First, Group By clause will Group the customers by Occupation and Education. Next, Having Clause will check the condition whether the SUM([Yearly Income]) > 60000.
The Where Clause is applied to each row before the SQL Server Group By implemented. However, SQL Having Clause is used after the Group by
SQL Having Max Example
In this example, we are using the Max Function in the Having Clause. It 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
SQL Having Max Example 2
In this SQL Having Clause 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, 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 Having Clause 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. The 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
SQL Having Multiple Conditions
This Sql Server having clause example uses Multiple Conditions in the Having Clause. The Group By statement will Group the customers by Occupation & 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
SQL Having Order By Example
The Having Clause 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
SQL Having Vs Where Clause
We can also use the Where Clause along with the Having Clause. 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.
-- 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
To explain the Sql Server Having Vs Where Clause, we are commenting the 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
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
