Building a report, one question at a time
A business report is rarely one giant query — it is a sequence of small questions, each answered by a single SELECT. "How big is each team?" "What is active work costing us?" "Which departments are spread too thin?" Tackle them one at a time, verify each answer, and the report assembles itself.
Every milestone here follows the same recipe. Pick the tables that hold the answer, join them on their foreign keys, filter down to the rows that matter, group by the thing you want one row per, and apply the right aggregate. The skill is not memorising syntax; it is translating a plain-English question into those five steps.
flowchart LR D[departments] --> J[JOIN + GROUP BY] E[employees] --> J P[projects] --> J J --> R[Report rows] style J fill:#0e7490,color:#fff style R fill:#0e7490,color:#fff
The order the database thinks in
SQL reads top to bottom but executes inside out. The clauses run in a fixed pipeline: FROM and JOIN assemble the rows first, then WHERE filters them, then GROUP BY buckets them, then HAVING filters those buckets, and only then do SELECT, ORDER BY, and LIMIT run.
This order explains two puzzles beginners hit constantly. You cannot use an aggregate like AVG(salary) in a WHERE clause, because WHERE runs before the grouping that creates the average. And you cannot reference a SELECT alias in WHERE either, for the same reason — the alias does not exist yet when WHERE runs. Once the pipeline is familiar, these restrictions stop feeling arbitrary and start feeling inevitable.
flowchart LR F["FROM + JOIN<br/>assemble rows"] --> W["WHERE<br/>filter rows"] W --> G["GROUP BY<br/>bucket rows"] G --> H["HAVING<br/>filter groups"] H --> S["SELECT<br/>pick and compute"] S --> O["ORDER BY<br/>sort"] O --> L["LIMIT<br/>trim"] style W fill:#0e7490,color:#fff style H fill:#b45309,color:#fff
WHERE versus HAVING — the report's key decision
Both clauses filter, and mixing them up is the most common reporting mistake. The difference is timing. WHERE removes rows before grouping, so it answers "which rows should I even consider?" HAVING removes groups after aggregation, so it answers "which results do I want to see?"
A milestone that sums the budget of active projects filters rows with WHERE — inactive projects never enter the sum. A milestone that lists only departments with more than one project filters groups with HAVING — every project counts, but only busy departments survive. Ask "does this filter apply before or after the aggregate?" and the right clause becomes obvious.
Milestone 1 — Team size and payroll
Start with the fundamentals: how big is each team, and what does it cost on average? Join departments to employees so each department meets its people, then group by department name to fold each team into a single summary row. Inside each group, COUNT(e.id) tallies the people and AVG(e.salary) averages their pay.
Counting a specific column rather than COUNT(*) is a good habit — it counts only real matches, which matters once a LEFT JOIN can contribute empty rows to a group. With an inner join the two agree here, but the discipline pays off the moment joins get asymmetric.
SELECT d.name, COUNT(e.id) AS headcount, AVG(e.salary) AS avg_salary FROM departments d JOIN employees e ON d.id = e.department_id GROUP BY d.name;
Milestone 2 — Active project spending
Now turn to the project portfolio. The question is what each department's active work costs, so filter to active projects before they enter the sum. This is WHERE followed by GROUP BY: individual project rows are trimmed first, then the survivors are totalled per department.
Notice that the Support department vanishes from this result. Its only project is on hold, so WHERE removes it before grouping, and a department with no surviving rows produces no group at all. A missing department in an aggregate report is almost always a filter excluding all of its rows — not a bug.
SELECT d.name, SUM(p.budget) AS total_active_budget FROM departments d JOIN projects p ON d.id = p.department_id WHERE p.status = 'active' GROUP BY d.name;
Milestone 3 — Portfolio breadth
The next question needs HAVING, not WHERE: which departments own more than one project? Every project must be counted first, and only then can you discard the departments whose count is too low. That is the signature of HAVING — the filter depends on an aggregate, so it must run after the grouping.
Group by department, count the projects, and keep only the groups whose count exceeds one. Engineering and Marketing both clear the bar; the single-project departments drop out. HAVING sees the aggregated value that WHERE cannot, because by the time HAVING runs the count already exists.
Milestone 4 — Ranking the teams
Reports usually need an order, not just a list. Ranking is simply ORDER BY applied to an aggregate column — ORDER BY avg_salary DESC sorts the grouped rows by their computed average, highest first. The aggregate has already been calculated by the time ORDER BY runs, so you can sort by it as freely as by any ordinary column.
Adding LIMIT 1 turns a ranking into a single answer: the one department with the highest average pay. Ranking plus limiting is the pattern behind every leaderboard, top-seller list, and employee-of-the-month query.
Milestone 5 — The single biggest bet
Combine everything for one headline number: which department holds the largest single project budget? Group projects by department, take the MAX budget in each group, then sort and limit to one row. MAX picks the largest value inside a group just as AVG averages — the same aggregate machinery, a different summary.
This milestone chains join, group, aggregate, order, and limit into a single query. That is the destination every report-building skill leads toward: one question, answered precisely, in one SELECT.
Milestone 1 — Team summary. For each department, return its name, the number of employees as headcount, and the average salary as avg_salary.
SELECT d.name, COUNT(e.id) AS headcount, AVG(e.salary) AS avg_salary FROM departments d JOIN employees e ON d.id = e.department_id -- finish the query ;
Group by department name so you get one row per department.
Use
COUNT(e.id)andAVG(e.salary)inside the groups.
SELECT d.name, COUNT(e.id) AS headcount, AVG(e.salary) AS avg_salary FROM departments d JOIN employees e ON d.id = e.department_id GROUP BY d.name;
Milestone 2 — Active spending. Return each department's name and the total budget of its active projects as total_active_budget. Only include active projects.
SELECT d.name, SUM(p.budget) AS total_active_budget FROM departments d JOIN projects p ON d.id = p.department_id -- filter and group here ;
Filter rows with
WHERE p.status = 'active'before grouping.Use
SUM(p.budget)andGROUP BY d.name.
SELECT d.name, SUM(p.budget) AS total_active_budget FROM departments d JOIN projects p ON d.id = p.department_id WHERE p.status = 'active' GROUP BY d.name;
Milestone 3 — Portfolio breadth. Return each department's name, the number of projects it owns as project_count, and its largest project budget as max_budget. Only include departments with more than one project.
SELECT d.name, COUNT(p.id) AS project_count, MAX(p.budget) AS max_budget FROM departments d JOIN projects p ON d.id = p.department_id -- group and filter groups here ;
Count projects with
COUNT(p.id)and find the biggest budget withMAX(p.budget).Use
HAVING COUNT(p.id) > 1to keep only departments with multiple projects.
SELECT d.name, COUNT(p.id) AS project_count, MAX(p.budget) AS max_budget FROM departments d JOIN projects p ON d.id = p.department_id GROUP BY d.name HAVING COUNT(p.id) > 1;
Milestone 4 — Team ranking. For each department, return its name and average salary as avg_salary, ranked from the highest average to the lowest.
SELECT d.name, AVG(e.salary) AS avg_salary FROM departments d JOIN employees e ON d.id = e.department_id -- group and rank here ;
Group by
d.nameto get one summary row per team.Rank with
ORDER BY avg_salary DESC— you can sort by an aggregate alias.
SELECT d.name, AVG(e.salary) AS avg_salary FROM departments d JOIN employees e ON d.id = e.department_id GROUP BY d.name ORDER BY avg_salary DESC;
Milestone 5 — Biggest bet. Return a single row: the name of the department whose largest project has the highest budget of any department, and that budget as max_budget.
SELECT d.name, MAX(p.budget) AS max_budget FROM departments d JOIN projects p ON d.id = p.department_id -- group, rank, and limit to one row here ;
Group by
d.namesoMAX(p.budget)finds each team's largest project.Sort by
max_budget DESCand keep the top row withLIMIT 1.
SELECT d.name, MAX(p.budget) AS max_budget FROM departments d JOIN projects p ON d.id = p.department_id GROUP BY d.name ORDER BY max_budget DESC LIMIT 1;
Recap
- A report is a sequence of small questions, each one SELECT — build and verify each step in turn.
- SQL runs a fixed pipeline: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.
- WHERE filters rows before grouping; HAVING filters groups after aggregation.
- If a filter depends on an aggregate such as COUNT or SUM, it must be HAVING.
- Sort by an aggregate with ORDER BY, and add LIMIT to turn a ranking into a single answer.
- A department missing from an aggregate result usually had all its rows filtered away.
You can now read a business question and decompose it into joins, filters, groups, and summaries — the foundation of almost every real-world SQL query.