Case Study: Building a Department Pay Report

One table. Six requirements, arriving one at a time. One finished query.

One table, six requirements, one query

Real reports arrive one requirement at a time, and each new requirement can quietly invalidate a decision made in the step before it. Building the query in stages — and re-checking earlier assumptions at each step — is the actual skill; the SQL syntax is the easy part.

  1. 1. Total pay per person, salary plus bonus. The pitfall is Salary + Bonus, which NULLs three employees. Correct: Salary + COALESCE(Bonus, 0) — but only after confirming that a missing bonus should be treated as zero rather than as "not yet known."

  2. 2. Only people earning over £70,000 total. The pitfall is WHERE TotalPay > 70000 — the alias doesn't exist yet when WHERE runs. Repeat the expression, or wrap step 1 in a CTE (see Reusable Expressions).

  3. 3. Average total pay per department. Easy to silently drop the £70,000 filter from step 2 while adding this. Confirm explicitly whether the average should span all staff or only the filtered group — the requirement genuinely changed, and this is scope, not syntax.

  4. 4. Only departments where that average exceeds £60,000. This condition depends on an aggregate, so it belongs in HAVING, not WHERE. Some engines allow reusing a SELECT alias inside HAVING and some don't — write the aggregate expression again to stay portable.

  5. 5. Sort by that average, highest first, limit to two. ORDER BY can use the alias here, because SELECT has already run by the time ORDER BY evaluates. LIMIT (or TOP) applies last of all. Worth stating out loud even without a real tie: what should happen if two departments land on the same average?

  6. 6. It now runs against 40 million rows and takes four minutes. The right first move is reading the execution plan, not guessing at a fix. Check whether the WHERE predicate is sargable, whether the filtered and grouped columns are indexed, and whether the report needs to be live at all or could be a nightly aggregate instead.

The assembled query

WITH pay AS (
  SELECT Department,
         Salary + COALESCE(Bonus, 0) AS TotalPay
  FROM Employees
)
SELECT Department,
       AVG(TotalPay) AS AvgTotalPay
FROM pay
GROUP BY Department
HAVING AVG(TotalPay) > 60000
ORDER BY AvgTotalPay DESC
LIMIT 2;   -- TOP 2 on SQL Server
Key takeaway

Each step in this walkthrough maps to exactly one earlier note: NULL handling, execution order, CTEs, HAVING vs. WHERE, and deterministic sorting. A report built like this is a composition of models already understood, not a new problem each time.

Try it

WITH pay AS (
  SELECT Department,
         Salary + COALESCE(Bonus, 0) AS TotalPay
  FROM Employees
)
SELECT Department,
       AVG(TotalPay) AS AvgTotalPay
FROM pay
GROUP BY Department
HAVING AVG(TotalPay) > 60000
ORDER BY AvgTotalPay DESC
LIMIT 2;