Topic 14 of 52
GROUP BY Clause
Overview
Aggregate functions alone are limited; they summarize the entire table into one number. But what if the CEO asks, 'What is our total revenue per department?' This is where GROUP BY shines. It commands the database to first sort the table into buckets (e.g., an 'HR' bucket, a 'Sales' bucket). THEN, it runs the aggregate function (SUM) completely independently inside each bucket.
Syntax
sql
-- 1. Basic Grouping
-- Summarize the total salary PER department
SELECT department, SUM(salary) AS total_payroll
FROM employees
GROUP BY department;
-- 2. Multi-Column Grouping
-- Summarize total sales PER year, and PER region!
SELECT year, region, SUM(revenue)
FROM sales
GROUP BY year, region
ORDER BY year DESC;
-- 3. Grouping with a WHERE filter
-- Filter the raw rows BEFORE grouping them!
SELECT department, COUNT(*) AS head_count
FROM employees
WHERE status = 'Active'
GROUP BY department;Common Pitfalls
- The Golden Rule of
GROUP BY: Every single column in yourSELECTclause MUST either be wrapped in an Aggregate Function (likeSUM), or explicitly listed in theGROUP BYclause. If you selectdepartment, role, SUM(salary)but onlyGROUP BY department, it will crash. - Grouping by a non-unique text column. If you
GROUP BY user_name, and you have five 'John Smith's in your database, SQL will merge all five of their orders into a single bucket. AlwaysGROUP BYthe primary key (user_id).
Interview Questions
Q:
In what exact order does SQL process a query containing
WHERE and GROUP BY?A:
1. FROM (Get table) -> 2. WHERE (Filter raw rows) -> 3. GROUP BY (Bucket the survivors) -> 4. SELECT (Run aggregates on buckets).
Real-World Example
An e-commerce query calculating the average order value (AOV) per country.
example
sql
SELECT
shipping_country,
COUNT(order_id) AS total_orders,
AVG(order_total) AS average_order_value
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY shipping_country
ORDER BY total_orders DESC;Check Your Knowledge
Test your understanding of GROUP BY Clause with these quick questions.