Grouping Data with GROUP BY

GROUP BY collapses multiple rows sharing the same value in one or more columns into a single summary row per group, typically paired with aggregate functions like COUNT, SUM, and AVG to compute a statistic for each group. This is the foundation of virtually every reporting and analytics query: totals by category, counts by status, averages by region.

Why it matters

Reports are questions about groups: sales per month, average salary per department, number of orders per customer. GROUP BY with aggregate functions answers them in one query. The difference between WHERE and HAVING is one of the most frequently asked SQL interview questions.

Aggregate Functions

Aggregate functions compute a single value from a set of rows.

• COUNT(*) — number of rows in the group; COUNT(column) counts non-NULL values only.
• SUM(column) — total of a numeric column across the group.
• AVG(column) — average of a numeric column across the group.
• MIN(column) / MAX(column) — smallest and largest value in the group.

How GROUP BY Works

GROUP BY column partitions the rows into groups sharing the same value in that column, and every column named in the SELECT list must either appear in the GROUP BY clause or be wrapped in an aggregate function — a database will reject a query that mixes a raw, non-aggregated column with GROUP BY unless it is one of the grouping columns.

HAVING vs. WHERE

WHERE filters individual rows before grouping happens, while HAVING filters entire groups after aggregation, based on the result of an aggregate function. You cannot write WHERE COUNT(*) > 5 because COUNT does not exist yet at the row-filtering stage — that condition belongs in HAVING, applied after the grouping and aggregation are computed.

Examples

SELECT department, COUNT(*) AS employee_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Returns 2 rows: ('Engineering', 2, 68000.00), ('Sales', 1, 55000.00)

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;

Returns 1 row: ('Engineering', 2) — Sales is excluded because its count of 1 does not satisfy HAVING COUNT(*) > 1.

Exercise

For each department, return the department, the number of staff as staff and the total salary as total, but only for departments that have at least two employees.

Expected output: department | staff | total then IT | 2 | 110000

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, department TEXT, salary INTEGER);
INSERT INTO employees (id, name, department, salary) VALUES
  (1, 'Asha', 'IT', 50000),
  (2, 'Ravi', 'IT', 60000),
  (3, 'Zoya', 'HR', 70000);

-- TODO: department, number of staff and total salary, for departments with 2 or more staff

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, department TEXT, salary INTEGER);
INSERT INTO employees (id, name, department, salary) VALUES
  (1, 'Asha', 'IT', 50000),
  (2, 'Ravi', 'IT', 60000),
  (3, 'Zoya', 'HR', 70000);

SELECT department, COUNT(*) AS staff, SUM(salary) AS total
FROM employees
GROUP BY department
HAVING COUNT(*) >= 2;

Quick quiz

Interview questions