What is the difference between HAVING and WHERE clause in SQL?

Solution

WHERE clause is used in SQL with the SELECT statement to filter the data on the basis of provided condition.

HAVING clause is also used to filter the data with the aggregate functions. Having clause is used to filter the data after the aggregate operation is performed.

Consider below table

Table name : Employees

Name Salary Age Department
Ashish 2000000 24 IT
Amit 5000000 31 IT
Saurabh 4000000 43 Finance
Andrew 20000 35 Admin
Allis 300000 35 IT
Bob 10000 23 Finance
Suresh 70000 43 Finance
Russle 4500000 46 HR

Now management wants to know the total salaries for departments whose total salary is greater the 1 million  

SELECT Department,
SUM(Salary) AS TotalSalary
FROM dbo.Employees
GROUP BY Department
HAVING TotalSalary > 1000000

Output

Department TotalSalary
IT 7300000
Finance 4800000

Above query get the desired result by applying the HAVING clause on aggregated salary SUM (salary).

Now management wants to know the total salaries for departments whose total salary is greater the 1 million but employee age is less than 30.

SELECT Department,
SUM(Salary) AS TotalSalary
FROM dbo.Employees
WHERE Age < 30
GROUP BY Department
HAVING TotalSalary > 1000000

Output

Department TotalSalary
IT 2000000

In the above query WHERE clause is applied to filter the employees whose age is <30, SQL will perform SUM after filtering the data. Finally, it will apply HAVING clause on aggregated data.

Share this thread

Comments

Comments
comments powered by Disqus

Navigation

Social Media