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.
Please Login to comment...