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: SQL 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:

• 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 table.
• 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 2014 with example.

Using Single Conditions in SQL Having

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

T-SQL CODE

In 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

Using Multiple Conditions in SQL Having

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

T-SQL CODE

In 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  and less than 200000.

OUTPUT

Using Where Clause along with SQL Having

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

T-SQL CODE

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

OUTPUT

Thank You for Visiting Our Blog