Group By Clause in SQL: Unveiling Patterns in Data

Estimated study time: 7 minutes. Turn row-level data into meaningful summaries.

The GROUP BY clause collapses multiple rows sharing a common value into a single summary row, which is what makes aggregate functions like COUNT, SUM, and AVG genuinely useful.

Basic Syntax

SELECT column, AGG_FUNCTION(other_column)
FROM table_name
GROUP BY column;

A Simple Example

To find the total salary paid per department:

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

Grouping by Multiple Columns

You can group by more than one column to get finer-grained summaries:

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

The Golden Rule

Every column in the SELECT list that isn't wrapped in an aggregate function must appear in the GROUP BY clause. Otherwise, SQL Server will raise an error because it doesn't know which value to show for that column.

Filtering Groups with HAVING

Since WHERE runs before grouping, filtering on an aggregated value needs HAVING instead:

SELECT Department, COUNT(*) AS HeadCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 5;