You are currently viewing How To Group Data Using Group By in SQL Server

How To Group Data Using Group By in SQL Server

How To Group Data Using Group By in SQL Server

Group By clause is used to group a selected set of rows into a set of summary rows by the values of one or more columns or expressions. It is always used in conjunction with one or more aggregate functions. Group by clause always works with an aggregate function like MAX, MIN, SUM, AVG, COUNT.

For example, you have a table, tblEmployee with EmpGender and EmpSalary columns along with other columns, and you want to calculate the salary of male and female gender separately, then we use group by clause.

GROUP BY DEMO

For this demo first we create a table and insert some values and then see some practical examples.

Let’s insert some value in the above table.

CREATE TABLE tblEmployee
(
	EmpID INT PRIMARY KEY NOT NULL,
	EmpFirstName NVARCHAR(20) NOT NULL,
	EmpLastName NVARCHAR(20) NOT NULL,
	EmpAge INT NULL,
	EmpGender NVARCHAR(10) NULL,
	EmpEmail NVARCHAR(50) NULL,
	EmpPhone NVARCHAR(20) NULL,
	EmpSalary INT NULL,
	EmpJoiningDate DATETIME NULL
)
GO
INSERT INTO tblEmployee
    (EmpID, EmpFirstName,EmpLastName,EmpAge,EmpGender,EmpEmail,EmpSalary,EmpJoiningDate)
VALUES
    (1,'john','dave',26,'male','john@gmail.com',26000,'2010-02-01'),
    (2,'kamran','akmal',35,'male','kami@gmail.com',25000,'2011-01-01'),
    (3,'umar','akmal',36,'male','umar@gmail.com',38000,'2015-01-01'),
    (4,'amar','sidhu',36,'male','amar@gmail.com',25000,'2016-05-05'),
    (5,'simran','sidhu',25,'female','simran@gmail.com',33000,'2018-01-01'),
    (6,'diyana','jeni',26,'female','diyana@gmail.com',50000,'2005-01-01'),
    (7,'karishma','rai',27,'female','krai@gmail.com',70000,'2004-01-01'),
    (8,'anam','chaudhary',29,'female','anam@gmail.com',25000,'2020-01-01'),
    (9,'Amelia','sidhu',56,'female','amelia@gmail.com',50000,'2015-01-01')
    GO
    

Find Total Employees by Gender

Find Total Salary by Gender

Find Average Salary by Gender

Recommended Readings