Every query you have written so far answered a question for you — the person who knows the tables. A report answers a question for someone who does not: a manager asking how much each department costs, an executive asking which projects are active. The shift is not really in the SQL itself but in how you shape its output. A raw result set lists columns called name and salary; a report hands over Department and Average Salary, sorted, rounded, and labelled in the reader's language. Building that bridge is a skill of its own, and it rests on one idea: the grain of a report. Get the grain wrong and the numbers are not merely untidy — they are wrong, often by a lot, while looking perfectly correct. This lesson teaches you to choose the grain first and decorate the output second.
flowchart LR Q["Raw rows<br/>one per employee"] --> G["Pick the grain<br/>one row per department"] G --> R["Grouped report<br/>headcount + total budget"] style G fill:#0e7490,color:#fff style R fill:#1e293b,color:#fff
Pick the grain before you touch the SQL
The grain is the answer to one question: what does a single row of this report represent? One row per employee. One row per department. One row per project. State that sentence out loud before you write a line of SQL, because every aggregate and every join flows from it. If the grain is one row per department, then a headcount column must count the employees inside that department, and a budget column must total the projects belonging to it. A report that mixes grains on one row — an employee-level detail glued to a department-level total — is where most wrong business numbers are born. Decide the grain first, and the rest of the query is mostly mechanics.
SELECT d.name AS department, SUM(p.budget) AS total_budget FROM departments d JOIN projects p ON p.department_id = d.id GROUP BY d.id, d.name;
Dress the output for its reader
Once the grain is fixed, three small habits turn a result set into a report. Alias every computed column so the reader sees total_budget, not SUM(p.budget). Round money and averages to a sensible precision — ROUND(AVG(e.salary), -3) snaps salaries to the nearest thousand, which is all anyone reading a summary needs. Replace NULLs with COALESCE before they reach a spreadsheet, because a blank cell in a department-size column reads as 'unknown' when it actually means 'zero people'. None of these change the numbers; they change whether a busy reader trusts the page in front of them. A report that ships raw expression names and stray NULLs looks unfinished, and unfinished looks unreliable.
SELECT d.name AS department,
COALESCE(ROUND(AVG(e.salary), -3), 0) AS avg_salary_k
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name;
flowchart LR A["SUM(salary)"] --> B["alias: avg_salary"] B --> C["ROUND to thousands"] C --> D["COALESCE NULL to 0"] D --> F["finished column"] style F fill:#0e7490,color:#fff
What GROUP BY does to the SELECT list
In a grouped report, every column you SELECT is either listed in the GROUP BY clause or wrapped in an aggregate such as SUM or COUNT. The SQL standard says asking for a bare e.name alongside SUM(e.salary) grouped by department is an error — whose single name should represent a whole department? SQLite is unusually lenient: instead of refusing, it silently returns some employee's name from each group, an arbitrary pick. The query runs, the number looks plausible, and the label is meaningless. So treat the rule as yours to enforce even when the engine will not: group by the columns that define your grain, and aggregate everything else. A column you neither grouped by nor aggregated is a bug waiting to mislead a reader.
Detail reports and summary reports
Not every report collapses rows. A detail report keeps one row per entity — every employee with their salary and department — and is meant to be scanned or exported. A summary report rolls those rows up to a coarser grain — one row per department with its averages and totals. The same tables serve both; only the grain differs. Ask which one the reader wants before you write: a manager comparing team costs needs a summary, while an administrator fixing a data-entry error needs the detail. Summaries answer questions; details support action. Choosing the wrong one wastes the reader's time as surely as a wrong number would.
flowchart TD E["One employee<br/>in a department"] --> P1["project A"] E --> P2["project B"] E --> P3["project C"] P1 --> O["3 output rows<br/>the employee counted 3 times"] P2 --> O P3 --> O style O fill:#b45309,color:#fff
WITH heads AS (
SELECT department_id, COUNT(*) AS headcount
FROM employees GROUP BY department_id
),
budget AS (
SELECT department_id, SUM(budget) AS total_budget
FROM projects GROUP BY department_id
)
SELECT d.name AS department,
COALESCE(h.headcount, 0) AS headcount,
COALESCE(b.total_budget, 0) AS total_budget
FROM departments d
LEFT JOIN heads h ON h.department_id = d.id
LEFT JOIN budget b ON b.department_id = d.id;
Ranking and top-N
A summary report often wants its best or worst rows at the top. Pair ORDER BY with LIMIT to surface the three biggest budgets or the two smallest departments: sort so the rows you care about rise to the top, then cut the rest away. Sorting by an aggregate alias like total_budget is allowed and reads cleanly. Without an explicit sort the row order is arbitrary, so a top-N query without ORDER BY is meaningless — it keeps a random slice rather than the leaders. Build the habit of sort, then limit; it answers most best-and-worst questions in one short step.
SELECT d.name AS department, SUM(p.budget) AS total_budget FROM departments d JOIN projects p ON p.department_id = d.id GROUP BY d.id, d.name ORDER BY total_budget DESC LIMIT 3;
Build a department-grain report: for each department show its name and the SUM of project budget. Call the total total_budget.
SELECT d.name AS department, -- sum of project budgets here AS total_budget FROM departments d JOIN projects p ON p.department_id = d.id -- add your GROUP BY here ;
The total is SUM(p.budget).
Group by the department: GROUP BY d.id, d.name.
SELECT d.name AS department, SUM(p.budget) AS total_budget FROM departments d JOIN projects p ON p.department_id = d.id GROUP BY d.id, d.name;
For each department, show name and the average salary rounded to the nearest thousand, aliased avg_salary_k. Use ROUND(value, -3) for the rounding.
SELECT d.name AS department, -- average salary rounded to thousands here AS avg_salary_k FROM departments d JOIN employees e ON e.department_id = d.id -- add your GROUP BY here ;
The average is AVG(e.salary).
Wrap it in ROUND(..., -3) to snap to the nearest thousand.
Group by d.id, d.name so each department is one row.
SELECT d.name AS department, ROUND(AVG(e.salary), -3) AS avg_salary_k FROM departments d JOIN employees e ON e.department_id = d.id GROUP BY d.id, d.name;
This report intends to show each department's headcount, but joining projects to employees multiplies each person once per project in their department — Engineering has three projects, so it reports far more than four people. Repair it so the headcount is correct.
SELECT d.name AS department, COUNT(*) AS headcount FROM departments d JOIN employees e ON e.department_id = d.id JOIN projects p ON p.department_id = d.id GROUP BY d.name ;
A headcount report does not need the projects table at all.
Drop the JOIN to projects and group by d.id, d.name.
Engineering should report 4, not 12.
SELECT d.name AS department, COUNT(*) AS headcount FROM departments d JOIN employees e ON e.department_id = d.id GROUP BY d.id, d.name;
For each department show name, headcount, and total_budget on one row, with neither number inflated by the other. Aggregate employees and projects in separate CTEs, then join both summaries to departments; replace any missing value with 0.
WITH heads AS (
-- count employees per department here
),
budget AS (
-- sum project budgets per department here
)
SELECT d.name AS department,
COALESCE(h.headcount, 0) AS headcount,
COALESCE(b.total_budget, 0) AS total_budget
FROM departments d
LEFT JOIN heads h ON h.department_id = d.id
LEFT JOIN budget b ON b.department_id = d.id;
In the heads CTE: SELECT department_id, COUNT(*) AS headcount FROM employees GROUP BY department_id.
In the budget CTE: SELECT department_id, SUM(budget) AS total_budget FROM projects GROUP BY department_id.
Join each CTE to departments on department_id so the two children never meet and inflate each other.
WITH heads AS (SELECT department_id, COUNT(*) AS headcount FROM employees GROUP BY department_id), budget AS (SELECT department_id, SUM(budget) AS total_budget FROM projects GROUP BY department_id) SELECT d.name AS department, COALESCE(h.headcount, 0) AS headcount, COALESCE(b.total_budget, 0) AS total_budget FROM departments d LEFT JOIN heads h ON h.department_id = d.id LEFT JOIN budget b ON b.department_id = d.id;
You join employees, projects, and departments, then run COUNT(*) grouped by department to get headcount. Why is the headcount wrong?
A one-to-many join from employees to projects repeats each employee once per project. COUNT(*) then counts those copies, so a department with three projects triples its people. Aggregate each child table separately, or drop the join you do not need.
Recap
- A report shapes query output for a reader who does not know the schema; label columns in their language.
- Declare the grain — what one row means — before writing SQL; every aggregate and join follows from it.
- Alias,
ROUND, andCOALESCEthe output so it reads as finished, not raw. - Beware the fanout: a one-to-many join repeats rows and inflates
COUNTandSUM; aggregate each child table separately first. - A detail report keeps every row; a summary report rolls them up. Pick the one the reader needs.
Next you will turn these report shapes into the actual KPIs — headcount, averages, and rates — that a business reads every morning.