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.

Case Statement in SQL Server

Usage of CASE – WHEN:

This is used to derive new set of values from the given input.

It can be used in various ways depending on the business usage.

You may compare it with IF THEN ELSE and Switch statement as it will provide the same functionality.

Syntax:

CASE

            WHEN <Condition> THEN <Result1>

            WHEN <Condition> THEN <Result2>

            WHEN <Condition> THEN <Result3>

            ELSE <Result4> --Optional

END

In above syntax ELSE is optional, if we don't specify this then it will populate the value as NULL in case nothing is matched with conditions specified in WHEN clause.

Example:

SELECT

       EmployeeID

       ,CASE

              WHEN EmployeeID = 1 THEN 'Anil'

              WHEN EmployeeID = 2 THEN 'Suresh'

              WHEN EmployeeID = 3 THEN 'Vijaya'

              WHEN EmployeeID = 4 THEN 'Koushik'

              ELSE 'NA'

       END AS EmpName

       ,DepartmentID

FROM

       dbo.Employee

If we see the result as per the case statement EmpName column is populated.

As EmployeeID = 5 is not satisfying any condition, name is populated as NA

Now, remove ELSE part and check what is the result.

SELECT

       EmployeeID

       ,CASE

              WHEN EmployeeID = 1 THEN 'Anil'

              WHEN EmployeeID = 2 THEN 'Suresh'

              WHEN EmployeeID = 3 THEN 'Vijaya'

              WHEN EmployeeID = 4 THEN 'Koushik'

       END AS EmpName

       ,DepartmentID

FROM

       dbo.Employee

As we discussed, it placed NULL for EmployeeID = 5 as it is not satisfying any condition specified in WHEN clause.

NOT operator in SQL Server

Introduction to NOT operator:

Mostly we will use this keyword with LIKE and IN operators which will revese the action does by them.

If you want to select the data whose EmployeeID is not in 1, 3, 5 then just add NOT before IN clause:

SELECT

       *

FROM

       dbo.Employee

WHERE

       EmployeeID NOT IN (1, 3, 5)

In the same way if you don't want to see the employees whose name is starting with A then add NOT before LIKE operator:

SELECT

       *

FROM

       dbo.Employee

WHERE

       EmployeeName NOT LIKE 'A%'