SQL HAVING Clause with Examples

In MSSQL, the HAVING clause is used to apply a filter on the result of  GROUP BY based on the specified condition. The conditions are Boolean type i.e. use of logical operators(AND, OR). This clause was included in SQL as the WHERE keyword failed when we use it with aggregate expressions. Having is a very generally used clause in SQL. Similar to WHERE it helps to apply conditions, but HAVING works with groups. If you wish to filter a group, the HAVING clause comes into action.

Some important points:

• Having clause is used to filter data according to the conditions provided.
• Having clause is generally used in reports of large data.  
• Having clause is only used with the SELECT clause.
• The expression in the syntax can only have constants.
• In the query, ORDER BY  is to be placed after the HAVING clause, if any.
• HAVING Clause implements in column operation.
• Having clause is generally used after GROUP BY.
• The GROUP BY clause is used to arrange required data into groups.

Syntax:

SELECT col_1, function_name(col_2)
FROM tablename
WHERE condition
GROUP BY column1, column2
HAVING Condition
ORDER BY column1, column2;

Here, the function_name is the name of the function used, for example, SUM(), AVG().

Example 1: 

Here first we create a database name as “Company”, then we will create a table named “Employee” in the database. After creating a table we will execute the query.

Step 1: Creating a database

CREATE DATABASE Company;

Step 2: To use this database

USE Company;

Step 3: Creating table

CREATE TABLE Employee(
   EmployeeId int,
   Name Varchar(20),
   Gender Varchar(20),
   Salary int,
   Department Varchar(20),
   Experience Varchar(20)
);

Add value into the table:

INSERT INTO Employee VALUES (1, 'Rachit', 'M', 50000, 'Engineering', '6 year')
INSERT INTO Employee VALUES (2, 'Shobit', 'M', 37000, 'HR', '3 year')
INSERT INTO Employee VALUES (3, 'Isha', 'F', 56000, 'Sales', '7 year')
INSERT INTO Employee VALUES (4, 'Devi', 'F', 43000, 'Management', '4 year')
INSERT INTO Employee VALUES (5, 'Akhil', 'M', 90000, 'Engineering', '15 year')

The final table is:

SELECT * FROM Employee;

Step 4: Execute the query

This employee table will help us understand the HAVING Clause. It contains employee IDs, Name, Gender, department, and salary. To Know the sum of salaries, we will write the query:

SELECT Department, sum(Salary) as Salary
FROM employee
GROUP BY department;  

Here is the result,

Now if we need to display the departments where the sum of salaries is 50,000 or more. In this condition, we will use HAVING Clause.

SELECT Department, sum(Salary) as Salary
FROM employee
GROUP BY department
HAVING SUM(Salary) >= 50000;  

Example 2:

Suppose, a teacher wants to announce the toppers in class. For this, she decides to reward every student who scored more than 95%. We need to group the database by name and their percentage and find out who scored more than 95% in that year. So for this first, we create a database name as “School”, and then we will create a table named “Student” in the database. After creating a table we will execute the query.

Step 1: Creating a database

CREATE DATABASE School;

Step 2: To use this database

USE School;

Step 3: Creating table

CREATE TABLE Student(
   student Varchar(20),
   percentage int
);

Add value into the table:

INSERT INTO Student VALUES ('Isha Patel', 98)
INSERT INTO Student VALUES ('Harsh Das', 94)
INSERT INTO Student VALUES ('Rachit Sha', 93)
INSERT INTO Student VALUES ('Sumedha', 98)
INSERT INTO Student VALUES ('Rahat Ali', 98)

The final table is:

SELECT * FROM Student;

Step 4: Execute Query

SELECT student, percentage
FROM Student
GROUP BY student, percentage
HAVING percentage > 95;

Here, three students named Isha, Sumedha, Rahat Ali have scored more than 95 %.

Further, we can also filter rows on multiple values using the HAVING clause. The HAVING clause also permits filtering rows using more than one aggregate condition.

SELECT student  
FROM Student
WHERE percentage > 90
GROUP BY student, percentage
HAVING SUM(percentage) < 1000 AND AVG(percentage) > 95;

This query returns the students who have more percentage than 95 and the sum of percentage is less than 1000.