GROUP BY

Introduction

The GROUP BY statement in SQL is used to arrange identical data into groups. It is usually combined with aggregate functions like COUNT, SUM, AVG, MAX, or MIN to perform calculations on each group.

Syntax

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1;

• column1 – the column you want to group by
• aggregate_function(column2) – function to calculate on grouped data
• table_name – the table containing your data
• WHERE condition – optional filter before grouping

Example 1: Counting Records

Suppose we have a table Employees:

EmployeeID	Department	Salary
1	HR	5000
2	IT	6000
3	HR	5500
4	IT	6200
5	Sales	4500

We want to count employees in each department:

SELECT Department, COUNT(EmployeeID) AS TotalEmployees
FROM Employees
GROUP BY Department;

Result:

Department	TotalEmployees
HR	2
IT	2
Sales	1

Example 2: Sum of Salaries by Department

To find total salary for each department:

SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department;

Result:

Department	TotalSalary
HR	10500
IT	12200
Sales	4500

---

Example 3: Average Salary by Department

To find average salary for each department:

SELECT Department, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department;

Result:

Department	AverageSalary
HR	5250
IT	6100
Sales	4500

---

Key Points

• GROUP BY is always used after WHERE but before ORDER BY
• All columns in SELECT that are not aggregated must be included in GROUP BY
• GROUP BY helps summarize data for reports and analysis

Practice Exercise

Using the Employees table, write SQL queries to:

1. Find the highest salary in each department
2. Count how many employees earn more than 5000 in each department
3. Find the average salary of IT department