Group By Clause in SQL Server

Group By Clause in SQL Server

In this article, I am going to discuss the Group by Clause in SQL Server with examples. The Group by Clause in SQL Server is used to divide the similar type of records or data as a group and then return. Please read the following articles before proceeding to this article.

Where Clause in SQL Server

Order By Clause in SQL Server

Top n Clause in SQL Server

If we use group by clause in the query then we should use grouping/ aggregate function such as count(), sum(), max(), min(), avg() functions.

When we implement group by clause first the data of the table will be divided into the separate group as per the column and later aggregate function will execute on each group data to get the result. That means first Group By clause is used to divide similar type of data as a group and then an aggregate function is applied to each group to get the required results.

Syntax
SELECT expression1, expression2, expression_n, 
       aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, expression_n;
Parameters or Arguments

expression1, expression2, expression_n: The expressions that are not encapsulated within an aggregate function must be included in the GROUP BY clause.

aggregate_function: The aggregate function is nothing but such as SUM, COUNT, MIN, MAX, or AVG functions that we should use while we are using the Group by Clause in SQL Server.

Tables: Tables is nothing but the name of the table or tables from which we want to retrieve the data. 

WHERE conditions: It is optional. If you want to retrieve the data based on some conditions then you need to specify such conditions using the Where Clause in SQL Server.

Create a Database table with test data

We are going to use the following Employee table to understand the Group By Clause in SQL Server

Group By Clause in SQL Server

Please use the below SQL Script to create and populate the Employee table with test data that we are going to use in this article.

-- Create Employee table
CREATE TABLE Employee
(
  ID INT PRIMARY KEY IDENTITY(1,1),
  Name VARCHAR(100),
  EmailID VARCHAR(100),
  Gender VARCHAR(100),
  Department VARCHAR(100),
  Salary INT,
  Age INT,
  CITY VARCHAR(100)
)
GO

--Insert some test data into Employee table
INSERT INTO Employee VALUES('PRANAYA','PRANAYA@G.COM','Male', 'IT', 25000, 30,'MUMBAI')
INSERT INTO Employee VALUES('TARUN','TARUN@G.COM','Male', 'Payroll', 30000, 27,'ODISHA')
INSERT INTO Employee VALUES('PRIYANKA','PRIYANKA@G.COM','Female', 'IT', 27000, 25,'BANGALORE')
INSERT INTO Employee VALUES('PREETY','PREETY@G.COM','Female', 'HR', 35000, 26,'BANGALORE')
INSERT INTO Employee VALUES('RAMESH','RAMESH@G.COM','Male','IT', 26000, 27,'MUMBAI')
INSERT INTO Employee VALUES('PRAMOD','PRAMOD@G.COM','Male','HR', 29000, 28,'ODISHA')
INSERT INTO Employee VALUES('ANURAG','ANURAG@G.COM','Male', 'Payroll', 27000, 26,'ODISHA')
INSERT INTO Employee VALUES('HINA','HINA@G.COM','Female','HR', 26000, 30,'MUMBAI')
INSERT INTO Employee VALUES('SAMBIT','HINA@G.COM','Male','Payroll', 30000, 25,'ODISHA')
INSERT INTO Employee VALUES('MANOJ','MANOJ@G.COM','Male','HR', 30000, 28,'ODISHA')
INSERT INTO Employee VALUES('SWAPNA','SWAPNA@G.COM','Female', 'Payroll', 28000, 27,'MUMBAI')
INSERT INTO Employee VALUES('LIMA','LIMA@G.COM','Female','HR', 30000, 30,'BANGALORE')
INSERT INTO Employee VALUES('DIPAK','DIPAK@G.COM','Male','Payroll', 32000, 25,'BANGALORE')
GO

WAQ to find total employees in the organization.

SELECT COUNT(*) AS TotalEmployee FROM Employee

Group By Clause in SQL Server

WAQ to find the number of employees working in each department in the company.
SELECT Department, COUNT(*) AS TotalEmployee
FROM Employee 
GROUP BY Department

When we execute the above query, it gives us the below output.

Group By Clause in SQL Server

NOTE: If we use Group By Clause in a query, first the data in the table will be divided into different groups based on the column specified in the group by clause and then execute the aggregate function on each group to get the results.

WAQ to find total salary in each department of the organization.
SELECT Department, TotalSalary = SUM(Salary) 
FROM Employee 
GROUP BY Department

When we execute the above query, it gives us the below output.

Group By Clause in SQL Server

WAQ to find the highest salary of each department in the organization.
SELECT Department, MaxSalary = MAX(SALARY) 
FROM Employee 
GROUP BY Department

When we execute the above query, it gives us the below output.

Group By Clause in SQL Server

While working with the group by clause in a query we need to follow or consider the following

  1. When the aggregate function is applied to a group it returns only a single value but each group can return a value.
  2. Use Group By clause only on a column which contains duplicate values, never applies it on unique columns.
Write a query to get the number of employees working in each Gender per each department.
SELECT Department, Gender, EmployeeCount = COUNT(*) 
FROM Employee 
GROUP BY Department, Gender 
ORDER BY Department

When we execute the above query, it gives us the below output.

Group By Clause in SQL Server

When we use multiple columns in a group by clause first data in the table is divided based on the first column of the group by clause and then each group is subdivided based on the second column of the group by clause and then the group function is applied on each inner group to get the result.

Write a query to get the highest salary of the organization.

SELECT MAX(Salary) as MaxSalary FROM Employee

Group By Clause in SQL Server

Write a Query for retrieving total salaries by city.

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

SELECT CITY, SUM(Salary) as TotalSalary 
FROM Employee
GROUP BY CITY

When we execute the above query, it gives us the following output.

Group By Clause in SQL Server

Note: If you omit, the group by clause and try to execute the query, you get an error – 

Group By Clause in SQL Server with examples

Write a Query for retrieving total salaries by city and by gender.

It’s possible to group by multiple columns. In this query, we are grouping first by city and then by gender. 

SELECT CITY, Gender, SUM(Salary) as TotalSalary 
FROM Employee
GROUP BY CITY, Gender

When we execute the above query, it gives us the below output.

Group By Clause in SQL Server

Write a Query for retrieving total salaries and the total number of employees by City, and by gender.

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

SELECT  City, Gender, SUM(Salary) as TotalSalary, 
    COUNT(ID) as TotalEmployees
FROM Employee
GROUP BY  CITY, Gender

Group By Clause in SQL Server

Write a query to find out the highest salary of each department along with the name of the employee.
SELECT Department, MAX(Salary) as Salary, Name 
FROM Employee
GROUP BY Department – Invalid

It will give the following error

Column ‘Employee.Name’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

NOTE: The above query will not be executed because using group by clause in a statement the select list can contain only three things

  1. Columns that are associated with GROUP BY clause.
  2. Aggregate or GROUP functions
  3. Constants

In our above query Name column does not fall under any of the three so it cannot be used in the select list.

SELECT Department, MAX(Salary) as Salary, Name 
FROM Employee
GROUP BY Department -- Invalid	

SELECT Department, MAX(SALARY), GETDATE() 
FROM Employee 
GROUP BY Department --Valid

SELECT Department, Gender, MAX(Salary), 'Hello' as FixedValue 
FROM Employee 
GROUP BY Department, Gender --Valid

In the next article, I will discuss Having Clause in SQL Server with examples.

SUMMARY

In this article, I try to explain Group By Clause in SQL Server steps by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *