Group by Having clause in sql

GROUP BY – HAVING:

GROUP BY will be used in SELECT statement to gather the data specified in one column and perform aggregations on one or more columns.

HAVING will be used in combination with GROUP BY to perform filtering on grouped data.

Without using GROUP BY we can't use HAVING.

Example1:

If you count to see the number of employees from India, then we can achieve it by using GROUP BY clause:

SELECT

       Country

       ,COUNT(*)

FROM

       dbo.Employee

WHERE Country = 'India'

GROUP BY Country

One observation we need to do over here is, we should only have those columns in SELECT list which we have mentioned in GROUP BY apart from aggregations.

If you observe the above query, we have mentioned only Country and Aggregate function in our select list as Country is available in GROUP BY clause. If you mention any other columns apart from Country below error will be displayed.

SELECT

       Country

       ,EmployeeID

       ,COUNT(*)

FROM

       dbo.Employee

WHERE Country = 'India'

GROUP BY Country

Error: Msg 8120, Level 16, State 1, Line 3

Column 'dbo.Employee.EmployeeID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

EmployeeID is not available in GROUP BY but we have mentioned that in SELECT clause that is the reason it gave error message.

Example2:

If you want to see the number of employees from different countries then,

SELECT

       Country

       ,COUNT(*) AS NoOfEmp

FROM

       dbo.Employee

GROUP BY Country

I have added salary column in Employee table and the data looks like below:

Now, perform different aggregations using GROUP BY on top of this table:

SELECT

       Country

       ,MAX(Salary) AS MaxSalary

       ,MIN(Salary) AS MinSalary

       ,AVG(Salary) AS AvgSalary

       ,SUM(Salary) AS SumSalary

       ,COUNT(*) AS NoOfEmp

FROM

       dbo.Employee

GROUP BY Country

We can add HAVING clause in case of filtering the aggregated data:

Example: In the above result, you want to see the details such as MAX, MIN, AVG etc.. salaries only if the number of employees in that country exceeds 2.

In this case the query is as follows:

SELECT

       Country

       ,MAX(Salary) AS MaxSalary

       ,MIN(Salary) AS MinSalary

       ,AVG(Salary) AS AvgSalary

       ,SUM(Salary) AS SumSalary

       ,COUNT(*) AS NoOfEmp

FROM

       dbo.Employee

GROUP BY Country

HAVING COUNT(*) > 2

If we see the result set, USA country details got removed because number of employees from that country are 2 and HAVING clause is filtering that data.