The GROUP BY clause is used to group rows that have the same values in one or more columns and apply aggregate functions to each group.

Common aggregate functions:

  • COUNT() – number of rows
  • SUM() – total value
  • AVG() – average value
  • MIN() – smallest value
  • MAX() – largest value

Basic syntax:

SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
GROUP BY column_name;

Any column in the SELECT list that is not an aggregate function must appear in the GROUP BY clause.

Step 1: Example table

Employees Table

EmployeeID Name Department Salary
1 John IT 5000
2 Jane HR 4500
3 Mike IT 6000
4 Sara HR 4700
5 Tom IT 5200

Step 2: GROUP BY with COUNT()

Count employees in each department:

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

Result:

Department TotalEmployees
IT 3
HR 2

Step 3: GROUP BY with SUM()

Calculate total salary per department:

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

Result:

Department TotalSalary
IT 15200
HR 9200

Step 4: GROUP BY with AVG(), MIN(), MAX()

SELECT
Department,
AVG(Salary) AS AverageSalary,
MIN(Salary) AS MinSalary,
MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY Department;

Result:

Department AverageSalary MinSalary MaxSalary
IT 5066.67 5000 6000
HR 4600 4500 4700

Step 5: GROUP BY with multiple columns

Group by Department and Salary:

SELECT Department, Salary, COUNT(*) AS CountPerSalary
FROM Employees
GROUP BY Department, Salary;

Result:

Department Salary CountPerSalary
IT 5000 1
IT 5200 1
IT 6000 1
HR 4500 1
HR 4700 1

Step 6: GROUP BY with WHERE clause

Filter rows before grouping:

SELECT Department, COUNT(*) AS TotalEmployees
FROM Employees
WHERE Salary > 4800
GROUP BY Department;

Result:

Department TotalEmployees
IT 3

WHERE filters rows before grouping.

Step 7: GROUP BY with HAVING clause

Filter after grouping using HAVING:

SELECT Department, COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 2;

Result:

Department TotalEmployees
IT 3

HAVING is used with aggregate functions, unlike WHERE.

Step 8: GROUP BY with ORDER BY

Sort grouped results:

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

Categorized in:

SQL Server,