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%'

LIKE operator in SQL Server

Usage of LIKE in SQL Server:

If we want to get the data which is partially matching then we need to use LIKE operator.

Example:

Get the list of employees whose name is starting with A

SELECT

       *

FROM

       dbo.Employee

WHERE

       EmployeeName LIKE 'A%'

Get the list of employees whose name is ending with K

SELECT

       *

FROM

       dbo.Employee

WHERE

       EmployeeName LIKE '%K'

Between and IN Operators in SQL Server

BETWEEN and IN comparison:

BETWEEN will be used whenever we want to select the data based on a range.

IN will be used, if we want to select the data that falls under some random values.

Example:

Fetch the employees whose ID is from 1 to 3:

SELECT

       *

FROM

       dbo.Employee

WHERE

       EmployeeID BETWEEN 1 AND 3

Fetch the employees whose ID is either 1 OR 3 OR 5

SELECT

       *

FROM

       dbo.Employee

WHERE

       EmployeeID IN (1, 3, 5)

If we don't have IN, BETWEEN clause we need to write the same query by using OR keyword which will increase length of your code.

SELECT

       *

FROM

       dbo.Employee

WHERE

       EmployeeID = 1

       OR EmployeeID = 3

       OR EmployeeID = 5

Introduction to SELECT statement in SQL Server

Introduction to SELECT statement:

It is a command in DQL and is used to fetch the data from database.

Below are the most commonly used keywords that we use while fetching the data:

  SEELCT

  FROM

  WHERE

  ORDER BY

We will have mainly two parts in SELECT statement:

  List of columns

  List of tables from which you want to retrieve these columns

Employee table:

Attributes/ Columns

EmployeeID

EmployeeName

DepartmentID

City

Country

SELECT …....... FROM Clause:

If you want to select:

all the columns from a table, use * in SELECT statement

Example:

            SELECT * FROM dbo.Employee – Here Employee is the table name

few columns from a table:

            SELECT

                        EmployeeID

                        ,EmployeeName

            FROM

                        dbo.Employee

           

WHERE.....

This is used for filtering out the data from your table and is useful when you want only part of the data.

Example:

If we need only the employees who are staying in India then we can get by using the below query

SELECT

       *

FROM

       dbo.Employee

WHERE

       Country='India'

If we don't want to see any employees who are from India then we can write:

SELECT

       *

FROM

       dbo.Employee

WHERE

       Country<>'India'

Order By:

            Used for sorting the data in ascending or descending format.

SQL is providing ASC and DESC for this purpose. ASC is the default sorting means id we don't mention anything it will order the data in ascending order.

SELECT

       *

FROM

       dbo.Employee

ORDER BY EmployeeName

If we see the result, data is sorted based on EmployeeName in ascending order as we didn't mention any ordering in Order BY clause.

SELECT

       *

FROM

       dbo.Employee

ORDER BY EmployeeName DESC

In the above result set, data sorted by using employee name in descending order.