Group By Clause in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Group By Clause in SQL Server with Examples

In this article, I am going to discuss the Group by Clause in SQL Server with examples. Please read our previous article where we discussed the Top n Clause in SQL Server in detail.

Group By Clause in SQL Server

The Group by Clause in SQL Server is used to divide similar types of records or data as a group and then return. If we use group by clause in the query then we should use grouping/aggregate function such as count(), sum(), max(), min(), and 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 types of data as a group and then an aggregate function is applied to each group to get the required results.

Syntax of Group By Clause in SQL Server

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

Parameters or Arguments used in Group By Clause in SQL Server:

1. expression1, expression2, expression_n: The expressions that are not encapsulated within an aggregate function must be included in the GROUP BY clause.
2. 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.
3. Tables: Tables are nothing but the name of the table or tables from which we want to retrieve the data.
4. 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.

Example to Understand Group By Clause in SQL Server

Let us understand SQL Server Group By Clause with Examples. We are going to use the following Employee table to understand the Group By Clause in SQL Server with Examples.

Please use the below SQL Script to create and populate the Employee table with sample 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 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

WAQ to find the number of employees working in each department in the company.

Here, we need to group the employees by department, and then we need to apply the count function to each group. The following SQL query exactly does the same.

SELECT Department, COUNT(*) AS TotalEmployee
FROM Employee 
GROUP BY Department

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

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 the 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.

WAQ to find the highest salary in 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.

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 that contains duplicate values, never applies it on unique columns.

Write a query to get the number of employees working in each Gender per 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.

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 for the organization.

SELECT MAX(Salary) as MaxSalary FROM Employee

Write a Query for retrieving total salaries by the city.

We are applying the SUM() aggregate function on the Salary column, and grouping by city column. This effectively adds all the 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.

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

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.

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

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 error as 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 the 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 am going to discuss Having Clause in SQL Server with examples. Here, in this article, I try to explain Group By Clause in SQL Server with some examples. I hope this article will help you with your needs. I would like to have your feedback. Please post your feedback, question, or comments about this article.