Group by in SQL with example

Group by in SQL with Example | 5 Easy Examples

by


The GROUP BY clause in SQL is used to group rows that have the same data, effectively clubbing together these similar rows into groups.

Group by in SQL

The GROUP BY clause in SQL Server is used to group the data from a table based on the same values using aggregate functions like SUM, AVG, COUNT, MAX, and MIN. You can use the GROUP BY clause to retrieve summary data.

It’s certainly used with aggregate functions (like SUM, COUNT, AVG, MAX, MIN) to perform calculations on these grouped data. Without aggregate functions, you cannot use the GROUP BY clause.

Table of Contents


Group by Clause is generally used with the Following Aggregate Functions,

Aggregate Functions

1.SUM
2.AGV
3.COUNT
4.MIN
5.MAX

Let’s see Aggregate Functions one by one with example.

To see these all group by clause and Aggregate function you must have tables and data into that tables.

Let’s Create table and insert data into the tables.

Suppose we have a table named employee with the following structure:

Create Script for Group in SQL

CREATE TABLE [dbo].[employee](
	[emp_id] [int] NULL,
	[Name] [varchar](50) NULL,
	[Pan] [varchar](10) NULL,
	[Address] [varchar](50) NULL,
	[City] [varchar](50) NULL
) ON [PRIMARY]
GO;

Insert script for Group by in SQL


INSERT [dbo].[employee] ([emp_id], [Name], [Pan], [Address], [City]) VALUES (98, N'John Dev', N'ASDP5487L', N'Galli no.1 mahad,shad mumbai', N'Mumbai')
INSERT [dbo].[employee] ([emp_id], [Name], [Pan], [Address], [City]) VALUES (154, N'John Dev', N'ASDP5487L', N'Galli no.1 mahad,shad mumbai', N'Mumbai')
INSERT [dbo].[employee] ([emp_id], [Name], [Pan], [Address], [City]) VALUES (1, N'John Dev', N'ADSG5487U', N'EAST ANDHERI mahad,SHOAL mumbai', N'Mumbai')
INSERT [dbo].[employee] ([emp_id], [Name], [Pan], [Address], [City]) VALUES (8, N'John Dev', N'TRDP5487I', N'Galli no.1 mahad,shad DELH', N'Delhi')
INSERT [dbo].[employee] ([emp_id], [Name], [Pan], [Address], [City]) VALUES (9, N'John Dev', N'ASGF5488L', N'Galli no.1 MALAD,WEST ', N'Delhi')
INSERT [dbo].[employee] ([emp_id], [Name], [Pan], [Address], [City]) VALUES (5, N'John Dev', N'ASDP5487U', N'Galli no.1 GUNTUR,PIUJ ', N'Delhi')
INSERT [dbo].[employee] ([emp_id], [Name], [Pan], [Address], [City]) VALUES (77, N'John Dev', N'AREP5487P', N'Galli no.1 PANJI,THANA ', N'Kalkatta')
INSERT [dbo].[employee] ([emp_id], [Name], [Pan], [Address], [City]) VALUES (457, N'John Dev', N'YTDP5498L', N'Galli no.1 VIMAN,GURUD ', N'Kalkatta')
INSERT [dbo].[employee] ([emp_id], [Name], [Pan], [Address], [City]) VALUES (77, N'John Dev', N'ASLK5480L', N'Galli no.1 WADI,PATTA ', N'Pune')
INSERT [dbo].[employee] ([emp_id], [Name], [Pan], [Address], [City]) VALUES (58, N'John Dev', N'ASDP5489L', N'Galli no.1 MESHO,PLOT ', N'Pune')
INSERT [dbo].[employee] ([emp_id], [Name], [Pan], [Address], [City]) VALUES (74, N'John Dev', N'TEDP5487G', N'Galli no.1 VINAH,NAGFAR ', N'Pune')
INSERT [dbo].[employee] ([emp_id], [Name], [Pan], [Address], [City]) VALUES (77, N'John Dev', N'USDP5487Y', N'Galli no.1 SAWTA,KURE ', N'Pune')
INSERT [dbo].[employee] ([emp_id], [Name], [Pan], [Address], [City]) VALUES (98, N'John hit', N'ASDP5487L', N'Galli no.1 mahad,shad mumbai', N'Mumbai')
INSERT [dbo].[employee] ([emp_id], [Name], [Pan], [Address], [City]) VALUES (154, N'ansjul lala', N'ASDP5487L', N'Galli no.1 mahad,shad mumbai', N'Mumbai')
INSERT [dbo].[employee] ([emp_id], [Name], [Pan], [Address], [City]) VALUES (1, N'shulkja ramesh', N'ADSG5487U', N'EAST ANDHERI mahad,SHOAL mumbai', N'Mumbai')
INSERT [dbo].[employee] ([emp_id], [Name], [Pan], [Address], [City]) VALUES (8, N'arti shukla', N'TRDP5487I', N' no.1 mahad,shad DELH', N'Delhi')
INSERT [dbo].[employee] ([emp_id], [Name], [Pan], [Address], [City]) VALUES (9, N'mahendra kazi', N'ASGF5488L', N'plot no.1/MALAD,WEST ', N'Delhi')
INSERT [dbo].[employee] ([emp_id], [Name], [Pan], [Address], [City]) VALUES (5, N'gupta bheka', N'ASDP5487U', N'mohalla no.1 GUNTUR,PIUJ ', N'Delhi')
INSERT [dbo].[employee] ([emp_id], [Name], [Pan], [Address], [City]) VALUES (77, N'roshan cahouha', N'AREP5487P', N'po no.8 PANJI,THANA ', N'Kalkatta')
INSERT [dbo].[employee] ([emp_id], [Name], [Pan], [Address], [City]) VALUES (457, N'laki naja', N'YTDP5498L', N'OS no.1 VIMAN,GURUD ', N'Kalkatta')
INSERT [dbo].[employee] ([emp_id], [Name], [Pan], [Address], [City]) VALUES (77, N'roy jobo', N'ASLK5480L', N'RS no.5 WADI,PATTA ', N'Pune')
INSERT [dbo].[employee] ([emp_id], [Name], [Pan], [Address], [City]) VALUES (58, N'joy robo', N'ASDP5489L', N'Galli no.1 MESHO,PLOT ', N'Pune')
INSERT [dbo].[employee] ([emp_id], [Name], [Pan], [Address], [City]) VALUES (74, N'johny feshn', N'TEDP5487G', N'Galli no.1 VINAH,NAGFAR ', N'Pune')
INSERT [dbo].[employee] ([emp_id], [Name], [Pan], [Address], [City]) VALUES (77, N'king ratan', N'USDP5487Y', N'Galli no.1 SAWTA,KURE ', N'Pune')
GO

Now, we have to find the number of employees with a region (City) wise summary, which means how many total employees are in each City.

To find the City-wise count of employees, write the below SQL script with the GROUP BY clause using the COUNT aggregate function.

Example 1. Group by with COUNT

Group by Example

select City, count(emp_id) as No_of_emp 
from employee
Group by City

Above SQL query will find City wise Number of counts for employee table.

Group by Example

Query Description

We have used the aggregate COUNT function to calculate the number of employees in the table using COUNT(emp_id) in the SELECT SQL statement. You can use any aggregate function in the SELECT statement as per your requirement. The COUNT function will count the number of employees in emp_id for every City.

We have used the GROUP BY clause for City to group the rows with the same City together.

Finally, SQL Server calculates and executes the query, showing the result with the count and GROUP BY clause

CityNo_of_emp
Delhi 6
Kalkatta 4
Mumbai 6
Pune 8
output

Group by in SQL server

Example 2. Group by with SUM

Let’s see another example of the GROUP BY clause with the SUM aggregate function.

SUM is an aggregate function used to calculate the total of columns in the SQL Server database.

GROUP BY is a SQL Server clause used to group rows based on columns defined in the GROUP BY clause.

Consider you have an employee table, and you need to calculate the total salary for each employee designation. You can use the SUM aggregate function to calculate the total salary and the GROUP BY clause to group the designations by total salary.

select EmployeeDesignation,
sum(EmployeeSalary) Total_Salary
from Employee
Group by EmployeeDesignation
group by with sum

Here we have used SUM Aggregate function to calculate total salary designation wise using Group by SQL clause.

EmployeeDesignation Total_Salary
Database Administrator 133748
Database Analyst 54000
Database developer45694
Network Engineer273635
Project Manager955454
Senior Developer 62000
Software Engineer132458
Testing Engineer52000
Dev ops65474
output

Example3. Group with AVG

The AVG (Average) aggregate function is used to calculate the average value for a selected column.

Consider the same example as with SUM. To calculate the average salary from the Employee_1 table, with Employee_Id wise, you can use the AVG function.

Here is an example SQL script:

select Employee_ID, AVG(Salary) AverageSalary_IDWise from Employee_1 Group by Employee_ID

select Employee_ID, AVG(Salary) AverageSalary_IDWise from Employee_1 Group by Employee_ID

Total Average Salary of All employee’s, To calculate All employee’s Total Average Salary write an SQL statement like Below.

Total AVG Salary

select AVG(Salary) As TotAverageSalary from Employee_1

select AVG(Salary) As TotAverageSalary from Employee_1

Group by with MIN

The MIN aggregate function is used to calculate the minimum value from a column. The MIN function will retrieve the lowest value from the specified column.

Example: MIN Aggregate Function

Consider the example where you need to find the employee with the shortest name length. Here is how you can write the SQL query:

Select EmployeeName from Employee_copy where LEN(EMployeeName)=(select MIN(LEN(EMployeeName)) from Employee_copy)

This script will find the employee whose name has the minimum length. It first calculates the length of each name and then uses a subquery to find the minimum length. The WHERE clause ensures that only the employee(s) with the shortest name length are retrieved.

MIN Aggregate Function

Select EmployeeName from Employee_copy where LEN(EMployeeName)=(select MIN(LEN(EMployeeName)) from Employee_copy)

Here we have used the LEN function to find the length of the Name column and then used the MIN function with LEN to find the employee with the shortest name length

SQL GROUP BY Statement