The GROUP BY Clause

Aggregating an entire table into one row is useful, but usually, we want to see statistics broken down by category—for example, total sales per region or average salary per department. This is where GROUP BY comes in.

Definition: The GROUP BY Clause

The GROUP BY clause groups rows that have the same values in specified columns into summary rows.

The Golden Rule of Aggregation

This is the most common source of errors for SQL beginners.

If you use an aggregate function (like SUM) in your SELECT list, every other column in that SELECT list MUST either be part of an aggregate function or appear in the GROUP BY clause.

Danger

The Non-Aggregated Column Error:
SELECT Department, Name, SUM(Salary) FROM Employees GROUP BY Department;

Why this fails: The database is collapsing 10 rows into 1 row for the “Engineering” department. It can show the SUM (one value), and it can show the Department name (one value). But which Name should it show? Alice? Bob? Charlie? It doesn’t know, so it throws an error.

Context: Logical Order Revisited

Remember the logical order of execution:

1. FROM
2. WHERE
3. GROUP BY
4. SELECT

Because GROUP BY happens before SELECT, the database first organizes the data into buckets (groups). Only then does it calculate the SUM or COUNT for each bucket.

Detailed Explanation: Syntax

GROUP BY Syntax

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

Grouping by Multiple Columns

You can group by multiple levels. For example, to see sales by Year AND Region: GROUP BY Year, Region

Example: Departmental Stats

We have an Employees table:

Name	Dept	Salary
Alice	IT	90000
Bob	Sales	70000
Charlie	IT	85000
Diana	Sales	65000

Task: Find the total payroll and headcount for each department.

Grouping Example

SELECT
  Dept,
  COUNT(*) AS Headcount,
  SUM(Salary) AS TotalPayroll
FROM Employees
GROUP BY Dept;

Output

Dept	Headcount	TotalPayroll
IT	2	175000
Sales	2	135000

Under the Hood: The Internal Grouping Process

When the engine encounters GROUP BY Dept:

1. Sorting/Hashing: It either sorts the table by Dept or builds a hash map where each unique Dept name is a key.
2. Bucketing: As it scans the rows, it puts each row into its respective bucket.
3. Aggregating: It updates the running totals (COUNT, SUM) for that specific bucket.
4. Finalization: Once the table scan is complete, it outputs one row for every bucket created.

Performance Tip: Filtering Early

Always filter as much data as possible using WHERE before the GROUP BY.

• WHERE reduces the number of rows that need to be sorted and bucketed.
• The less data the GROUP BY has to process, the faster your query will run.

Tip

You can use expressions in a GROUP BY. For example, to group by month from a date:
GROUP BY EXTRACT(MONTH FROM OrderDate)