Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Python Programs
    • Java Programs
    • SQL FAQ’s

SQL HAVING Clause

by suresh

The SQL Having Clause is used to restrict the number of rows (or records) returned by the Group By Clause. In this article, we will show you, 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.

TIP: 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)
  • 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: SQL JOINs are used to join multiple tables.
  • Conditions: Here, we have to provide filters or conditions. If the condition is TRUE, then only the 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. Please refer to Group By Clause before looking into Having Clause.

We are going to use the below-shown data to explain the Having Clause in SQL Server with an example.

SQL Having Clause 1

SQL Having Single Condition Examples

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

SQL Having Sum Example

In this example, we are going to 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

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

OUTPUT

SQL Having Clause 4

NOTE: 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. 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 Clause 2

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. Select statement selects the Education, Maximum yearly Income, and Maximum Sales.

OUTPUT

SQL Having Clause 3

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 

OUTPUT

SQL Having Clause 6

SQL Having Multiple Conditions

In this Sql Server having clause 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 & Education. Next, SQL 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 Clause 5

SQL Having Order By Example

The SQL Server 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

OUTPUT

SQL Having Clause 7

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 

OUTPUT

SQL Having Clause 8

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 

OUTPUT

SQL Having Clause 9

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

SQL Having Clause 10

Placed Under: SQL

  • Install SQL Server
  • Install SQL Management Studio
  • Uninstall Management Studio
  • Install AdventureWorks Database
  • SQL Management Studio Intro
  • Connect SQL with sqlcmd utility
  • SQL Attach Database
  • SQL Detach Database
  • SQL Restore Database
  • Restore Database using BAK
  • SQL Rename Database with Files
  • Get SQL Database Names
  • SQL Create Table
  • SQL Rename Table
  • SQL Alter Table
  • SQL Add Column
  • SQL Rename Column
  • Get SQL Table Names in a DB
  • Find SQL Table Dependencies
  • Rename SQL Table & Column
  • SQL Global & Local Temp Table
  • SQL Table Variable
  • SQL Derived Table
  • SQL DATALENGTH
  • SQL Data Types
  • DML, DDL, DCL & TCL Cmds
  • SQL Query Builder
  • SQL ALIAS
  • SQL SELECT Statement
  • SQL SELECT DISTINCT
  • SQL SELECT INTO Statement
  • SQL INSERT Statement
  • SQL INSERT INTO SELECT
  • SQL BULK INSERT or BCP
  • SQL UPDATE Statement
  • SQL UPDATE from SELECT
  • SQL DELETE Statement
  • SQL TRUNCATE Table
  • SQL CASE Statement
  • SQL MERGE Statement
  • SQL Subquery
  • SQL CTE
  • SQL PIVOT
  • SQL UNPIVOT
  • SQL Clauses Examples
  • SQL TOP Clause
  • SQL WHERE Clause
  • SQL ORDER BY Clause
  • SQL GROUP BY Clause
  • SQL HAVING Clause
  • SQL Primary Key
  • SQL Foreign Key
  • SQL Referential Integrity
  • SQL Check Constraint
  • SQL Unique Constraint
  • SQL Default Constraint
  • SQL Clustered Index
  • SQL Non Clustered Index
  • SQL Filtered Indexes
  • SQL COALESCE Function
  • SQL IS NOT NULL
  • SQL IS NULL Function
  • SQL ISNULL
  • SQL JOINS
  • SQL CROSS JOIN
  • SQL FULL JOIN
  • SQL SELF JOIN
  • SQL Outer Joins
  • SQL Cross Join Vs Inner Join
  • SQL LEFT JOIN
  • SQL RIGHT JOIN
  • SQL AND & OR Operators
  • SQL Arithmetic Operators
  • SQL BETWEEN Operator
  • SQL Comparison Operators
  • SQL LIKE
  • SQL EXCEPT
  • SQL EXISTS Operator
  • SQL NOT EXISTS Operator
  • SQL INTERSECT
  • SQL IN Operator
  • SQL NOT IN Operator
  • SQL UNION
  • SQL UNION ALL
  • SQL IF ELSE
  • SQL ELSE IF
  • SQL WHILE LOOP
  • SQL Nested While Loop
  • SQL BREAK Statement
  • SQL CONTINUE Statement
  • SQL GOTO Statement
  • SQL IIF Function
  • SQL CHOOSE Function
  • SQL Change Data Capture
  • SQL Table Partitioning
  • SQL Table Partition using SSMS
  • SQL TRY CATCH
  • SQL VIEWS
  • SQL User Defined Functions
  • SQL Alter User Defined Functions
  • SQL Stored Procedure Intro
  • Useful System Stored Procedures
  • SQL SELECT Stored Procedure
  • SQL INSERT Stored Procedure
  • SQL UPDATE Stored Procedure
  • Stored Procedure Return Values
  • Stored Procedure Output Params
  • Stored Procedure Input Params
  • Insert SP result into Temp Table
  • SQL Triggers Introduction
  • SQL AFTER INSERT Triggers
  • SQL AFTER UPDATE Triggers
  • SQL AFTER DELETE Triggers
  • SQL INSTEAD OF INSERT
  • SQL INSTEAD OF UPDATE
  • SQL INSTEAD OF DELETE
  • SQL STATIC CURSOR
  • SQL DYNAMIC CURSOR
  • SQL FORWARD_ONLY Cursor
  • SQL FAST_FORWARD CURSOR
  • SQL KEYSET CURSOR
  • SQL TRANSACTIONS
  • SQL Nested Transactions
  • SQL ACID Properties
  • Create SQL Windows Login
  • Create SQL Server Login
  • SQL Server Login Error
  • Create SQL Server Roles
  • SQL Maintenance Plan
  • Backup SQL Database
  • SQL Ranking Functions Intro
  • SQL RANK Function
  • SQL PERCENT_RANK Function
  • SQL DENSE_RANK Function
  • SQL NTILE Function
  • SQL ROW_NUMBER
  • SQL Aggregate Functions
  • SQL Date Functions
  • SQL Mathematical Functions
  • SQL String Functions
  • SQL CAST Function
  • SQL TRY CAST
  • SQL CONVERT
  • SQL TRY CONVERT
  • SQL PARSE Function
  • SQL TRY_PARSE Function
  • SQL Calculate Running Total
  • SQL Find Nth Highest Salary
  • SQL Reverse String
  • SQL FOR XML PATH
  • SQL FOR XML AUTO
  • SQL FOR XML RAW
  • C Tutorial
  • C# Tutorial
  • Java Tutorial
  • JavaScript Tutorial
  • Python Tutorial
  • MySQL Tutorial
  • SQL Server Tutorial
  • R Tutorial
  • Power BI Tutorial
  • Tableau Tutorial
  • SSIS Tutorial
  • SSRS Tutorial
  • Informatica Tutorial
  • Talend Tutorial
  • C Programs
  • C++ Programs
  • Java Programs
  • Python Programs
  • MDX Tutorial
  • SSAS Tutorial
  • QlikView Tutorial

Copyright © 2021 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy