Having Clause in SQL Server

Having Clause in SQL Server

In this article, I am going to discuss the Having Clause in SQL Server with different types of examples. The Having Clause in SQL Server is also used for restricting or you can say filtering the data just like the where clause in SQL Server. 

Please read the following articles before proceeding to this article.

Where Clause in SQL Server

Order By Clause in SQL Server

Top n Clause in SQL Server

Group By Clause in SQL Server

You can say that the HAVING Clause in SQL Server is an additional filter that is applied to the result set. Logically, this having clause filters the rows from the intermediate result set that is built by using the FROM, WHERE, or GROUP BY clauses in the SELECT statement.

In SQL Server, the Having clause is typically used with a GROUP BY clause.  That means it is used in combination with a GROUP BY clause to restrict the number of groups to be returned by satisfying the condition which is specified using the having clause.

Syntax
SELECT expression1, expression2, expression_n, 
       aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, expression_n
HAVING having_condition;
Parameters or Arguments of Having Clause in SQL Server:

aggregate_function: It can be any of the aggregate function such as SUM, COUNT, MIN, MAX, or AVG.

expression1, expression2, expression_n: The expressions which are not encapsulated within an aggregate function must be included in the GROUP BY clause.

WHERE conditions: It is optional. If you want to retrieve selected records based on some conditions then you need to specify the conditions using the Where clause in SQL Server.

HAVING having_condition: The Having Clause Condition us used to add a further condition that can be applied only to the aggregated results to restrict the number of groups to be returned. 

Database tables used in this article:

We are going to use the following Employee table in this demo.

Having Clause in SQL Server

Please Use below script to create and populate the Employee table with some text data.

-- Create Person table
CREATE TABLE Employee
(
  ID INT PRIMARY KEY IDENTITY(1,1),
  Name VARCHAR(100),
  EmailID VARCHAR(100),
  Gender VARCHAR(100),
  Department VARCHAR(100),
  Salary INT,
  Age INT,
  CITY VARCHAR(100)
)
GO

--Insert some test data into Person table
INSERT INTO Employee VALUES('PRANAYA','PRANAYA@G.COM','Male', 'IT', 25000, 30,'MUMBAI')
INSERT INTO Employee VALUES('TARUN','TARUN@G.COM','Male', 'Payroll', 30000, 27,'ODISHA')
INSERT INTO Employee VALUES('PRIYANKA','PRIYANKA@G.COM','Female', 'IT', 27000, 25,'BANGALORE')
INSERT INTO Employee VALUES('PREETY','PREETY@G.COM','Female', 'HR', 35000, 26,'BANGALORE')
INSERT INTO Employee VALUES('RAMESH','RAMESH@G.COM','Male','IT', 26000, 27,'MUMBAI')
INSERT INTO Employee VALUES('PRAMOD','PRAMOD@G.COM','Male','HR', 29000, 28,'ODISHA')
INSERT INTO Employee VALUES('ANURAG','ANURAG@G.COM','Male', 'Payroll', 27000, 26,'ODISHA')
INSERT INTO Employee VALUES('HINA','HINA@G.COM','Female','HR', 26000, 30,'MUMBAI')
INSERT INTO Employee VALUES('SAMBIT','HINA@G.COM','Male','Payroll', 30000, 25,'ODISHA')
INSERT INTO Employee VALUES('MANOJ','MANOJ@G.COM','Male','HR', 30000, 28,'ODISHA')
INSERT INTO Employee VALUES('SWAPNA','SWAPNA@G.COM','Female', 'Payroll', 28000, 27,'MUMBAI')
INSERT INTO Employee VALUES('LIMA','LIMA@G.COM','Female','HR', 30000, 30,'BANGALORE')
INSERT INTO Employee VALUES('DIPAK','DIPAK@G.COM','Male','Payroll', 32000, 25,'BANGALORE')
GO
Filtering Groups:

The WHERE clause in SQL Server is used to filter the rows before aggregation, whereas the HAVING clause in SQL Server is used to filter the groups that mean after aggregations. The following 2 queries produce the same result.

Filtering rows using WHERE clause

SELECT City, SUM(Salary) as TotalSalary
FROM Employee
WHERE City = 'MUMBAI'
GROUP BY City

Filtering groups using HAVING clause, after all, aggregations take place.

SELECT City, SUM(Salary) as TotalSalary
FROM Employee
GROUP BY City
HAVING City = 'MUMBAI'

From the performance point of view, we cannot say or we cannot give a guarantee that one method is more efficient than the other one. The SQL Server optimizer checks or analyzes each statement and then selects an efficient way of executing the query. So, try to eliminate the records as soon as possible which you don’t want in your result set.

It is also possible to combine WHERE and HAVING
SELECT City, SUM(Salary) as TotalSalary
FROM Employee
WHERE Gender = 'Male'
GROUP BY City
HAVING City = 'MUMBAI'
Example – Using SUM function:

The below example uses the SUM function to return the name of the department and the total Salary (in associated with the department). The SQL Server HAVING clause will filter the results so that only departments with total Salary greater than 140000 will be returned.

SELECT Department, SUM(Salary) AS Total_Salary
FROM Employee
GROUP BY Department
HAVING SUM(Salary) > 140000;

Having Clause in SQL Server

Example – Using COUNT function

The below example uses the COUNT function to return the city and the number of employees (residing in that city). The SQL Server HAVING clause will filter the results so that only cities with more than 4 employees will be returned.

SELECT City, COUNT(*) AS 'Number of employees'
FROM Employee
GROUP BY City
HAVING COUNT(*) > 4;

Having Clause in SQL Server

Example – Using MIN function

The below example uses the MIN function to return the name of each department and the minimum salary in the department. The SQL Server HAVING clause will return only those departments where the minimum salary is greater than 25000.

SELECT Department, MIN(Salary) AS 'Lowest salary'
FROM Employee
GROUP BY Department
HAVING MIN(Salary) > 25000;

Having Clause in SQL Server

Example – Using MAX function

The below example uses the MAX function to return the name of each department and the maximum salary in each department. The SQL Server HAVING clause will return only those departments where the maximum salary is greater than 27000.

SELECT Department, MAX(Salary) AS 'Highest salary'
FROM Employee
GROUP BY Department
HAVING MAX(Salary) > 27000;

Having Clause in SQL Server

Example – Using AVG function

The below example uses the AVG function to return the name of each department and the Average salary of each department. The SQL Server HAVING clause will return only those departments where the average salary is greater than 27000.

SELECT Department, AVG(Salary) AS 'Average salary'
FROM Employee
GROUP BY Department
HAVING AVG(Salary) > 27000;

Having Clause in SQL Server

In the next article, I will discuss the differences between the having and where clauses in SQL Server.

SUMMARY

In this article, I try to explain Having Clause in SQL Server steps by step with some simple examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *