SQL Server GROUP BY Statement

SQL Server GROUP BY Statement

The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".

The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

SQL Script to create Employee and Department tables.

Create table Department
(
Dep_Id int primary key identity(1,1),
Department_Name varchar (50),
Location varchar (50),
Department_Head varchar (50)
)


insert into Department values('IT','London','Rick')
insert into Department values('Payroll','Delhi','Ron')
insert into Department values('HR','New York','Ravi')
insert into Department values('Other Department','Mumbai','Pratik')


Create table Employee
(
Emp_Id int primary key identity(1,1),
Name varchar (50),
Gender varchar(50),
salary int,
Department_Id int references Department(Dep_Id)  
)


insert into Employee values('Top','Male',20000,1)
insert into Employee values ('Pam', 'Female', 3000, 3)
Insert into Employee values ( 'John', 'Male', 3500, 1)
Insert into Employee values ('Sam', 'Male', 4500, 2)
Insert into Employee values ( 'Todd', 'Male', 2800, 2)
Insert into Employee values ('Ben', 'Male', 7000, 1)
Insert into Employee values ('Sara', 'Female', 4800, 3)
Insert into Employee values ( 'Valarie', 'Female', 5500, 1)
Insert into Employee values ( 'James', 'Male', 6500, NULL)
Insert into Employee values ( 'Russell', 'Male', 8800, NULL)

                                                          Department Table 

                                                          Employee Table

Write a query, to retrieve total salaries paid by Gender, The output should be as shown below.

Query for retrieving total salaries by Gender.

select  Gender, Sum(salary) as TotalSalary from Employee
group by Gender

We are applying SUM() aggregate function on Salary column, and grouping by Gender column. This effectively adds, all salaries of employees with in the same Gender

Note: If you omit, the group by clause and try to execute the query, you get an error - Column 'tblEmployee.City' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Write a query, to retrieve total salaries paid by Gender and total number of Employees by Gender, The output should be as shown below.

Query for retrieving total salaries and total number of employees by Gender.

select  Gender, sum(salary) as TotalSalary, count(Emp_Id) as TotalEmployee
from Employee
group by Gender

The only difference here is that, we are using Count() aggregate function.


Filtering Groups:
WHERE clause is used to filter rows before aggregation, where as HAVING clause is used to filter groups after aggregations. The following 2 queries produce the same result.

Filtering rows using WHERE clause, before aggrgations take place:

select  Gender, sum(salary) as TotalSalary, count(Emp_Id) as totalEmployee
from Employee
where Gender='Male'
group by Gender

Filtering groups using HAVING clause, after all aggrgations take place:

select  Gender, sum(salary) as TotalSalary, count(Emp_Id) as totalEmployee
from Employee
group by Gender
having Gender='Male'

Write a query, to retrieve Minimum salaries paid by Department using join. The output should be as shown below.

Query for retrieving minimum salaries by Department Name.

select Department.Department_Name, Min(Employee.salary) as MinimumSalary
from Department
join Employee
on 
Department.Dep_Id= Employee.Department_Id
group by Department.Department_Name

Write a query, to retrieve Minimum salaries paid by Department and total number of employees using join. The output should be as shown below.

Query for retrieving minimum salaries by Department Name and total number of employee.

select Department.Department_Name, Min(Employee.salary)
as MinimumSalart, count(Employee.Emp_Id) as TotalEmployee
from Department
join Employee
on 
Department.Dep_Id= Employee.Department_Id
group by Department.Department_Name

The only difference here is that, we are using Count() aggregate function.
If you want to retrieve Max salary of employee then just replace MIN to MAX in query.

Filtering rows using WHERE clause, before aggrgations take place:

select Department.Department_Name, Min(Employee.salary) as MinimumSalart,
count(Employee.Emp_Id) as TotalEmployee
from Department
join Employee
on 
Department.Dep_Id= Employee.Department_Id
where Department.Department_Name='IT'
group by Department.Department_Name

OR

select Department.Department_Name, Min(Employee.salary) as MinimumSalart,
count(Employee.Emp_Id) as TotalEmployee
from Department
join Employee
on 
Department.Dep_Id= Employee.Department_Id
where Department.Dep_Id=1
group by Department.Department_Name