A manager rarely wants a list. They want a number: How many people work here? What does the average engineer cost? What share of our projects is active? Each of those is a key performance indicator — a single figure that summarises how some part of the business is doing. A KPI is not a new SQL feature; it is what you get when you point the aggregates you already know — COUNT, SUM, AVG — at a question someone cares about. The skill is choosing the right aggregate and assembling it into an honest ratio. Get it right and a dashboard tile reads true; get the arithmetic wrong and the tile still shows a number, just a quietly false one. This lesson builds the common KPIs from the company dataset and, along the way, meets the two traps that ruin them: integer division and NULLs inside aggregates.
flowchart LR R["employee rows"] --> A["COUNT(*) / SUM(salary) / AVG(salary)"] A --> K["KPI numbers"] style K fill:#0e7490,color:#fff
Headcount, payroll, and averages
The three foundations are COUNT for how many, SUM for a total, and AVG for a typical value. Wrap them over the whole table for a company-wide KPI, or pair them with GROUP BY for one figure per department. SELECT COUNT(*) FROM employees is the total headcount; add GROUP BY department_id and the same expression becomes a per-team headcount. SUM(salary) is the payroll; AVG(salary) is what a typical person earns. Notice how one aggregate answers many questions depending on whether it runs alone or inside a group. The number itself is trivial to compute; the discipline is naming it in business terms — headcount, payroll, average cost per head — so the reader knows which question it answers.
SELECT d.name AS department,
COUNT(*) AS headcount,
ROUND(AVG(e.salary), 0) AS avg_salary
FROM departments d
JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name;
The trap at the heart of every ratio: integer division
Most interesting KPIs are ratios — a share, a rate, a percentage. In SQLite, dividing two integers yields an integer, truncating the remainder: 7 / 2 is 3, and 4 / 15 is 0. So a headcount percentage written COUNT(*) / (SELECT COUNT(*) FROM employees) returns 0 for every department, because both operands are whole numbers. The cure is to force floating-point arithmetic before the division happens. Multiply the top by 100.0 — COUNT(*) * 100.0 / total — and SQLite keeps the fraction, returning 26.7 instead of 0. The trailing .0 is the whole fix; forgetting it is the single most common reason a percentage KPI reads zero. Whenever a ratio comes back as zero, check the types before you check the logic.
flowchart TD A["4 / 15"] -->|integer division| Z["0<br/>fraction thrown away"] B["4 * 100.0 / 15"] -->|float arithmetic| RR["26.7<br/>percentage survives"] style Z fill:#b45309,color:#fff style RR fill:#0e7490,color:#fff
SELECT d.name AS department,
ROUND(SUM(p.budget) * 100.0 / (SELECT SUM(budget) FROM projects), 1) AS pct_of_total_budget
FROM departments d
JOIN projects p ON p.department_id = d.id
GROUP BY d.id, d.name;
Counting only the rows that matter
A raw COUNT(*) counts every row, but a KPI often asks about a subset: how many projects are active, how many employees earn above a threshold. The tool is CASE WHEN, used as a per-row switch inside an aggregate. SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) adds one for each active project and zero for the rest, so the total is exactly the active count — no WHERE needed, and crucially you can count several conditions in the same query. Because the CASE sits inside SUM, the row is never actually filtered out; it simply contributes zero. That is what lets one report show active, completed, and on-hold counts side by side, each its own CASE inside its own SUM.
flowchart LR P1["project active"] --> C1["CASE: 1"] P2["project on hold"] --> C0["CASE: 0"] P3["project completed"] --> C0B["CASE: 0"] C1 --> S["SUM = active count"] C0 --> S C0B --> S style S fill:#0e7490,color:#fff
SELECT d.name AS department,
SUM(CASE WHEN p.status = 'active' THEN 1 ELSE 0 END) AS active_projects
FROM departments d
LEFT JOIN projects p ON p.department_id = d.id
GROUP BY d.id, d.name;
What this dataset can and cannot tell you
Real billable utilization — hours worked against hours available — needs a timesheet table this company does not have, so we will not invent one. What the schema does support are honest proxy KPIs: how many people each department fields, how much project budget sits behind each head, and what share of projects is active. When a KPI you are asked for needs data that is not there, say so rather than substituting a number that looks similar. A ratio computed from the wrong table is worse than no ratio at all, because it carries false precision. The exercises below build the proxies that are defensible and leave the rest honestly unanswered.
Build a per-department report showing each department's name, its headcount, and its total payroll (the SUM of salaries). Group by department.
SELECT d.name AS department, -- headcount and payroll here FROM departments d JOIN employees e ON e.department_id = d.id -- add your GROUP BY here ;
Headcount is COUNT(*); payroll is SUM(e.salary).
Group by d.id, d.name so each department is one row.
SELECT d.name AS department, COUNT(*) AS headcount, SUM(e.salary) AS payroll FROM departments d JOIN employees e ON e.department_id = d.id GROUP BY d.id, d.name;
For each department, count how many of its projects are active. Use SUM(CASE WHEN p.status = 'active' THEN 1 ELSE 0 END) aliased active_projects. Keep every department, even one with no active projects (it should read 0).
SELECT d.name AS department, -- conditional count of active projects here AS active_projects FROM departments d LEFT JOIN projects p ON p.department_id = d.id -- add your GROUP BY here ;
Use SUM(CASE WHEN p.status = 'active' THEN 1 ELSE 0 END).
LEFT JOIN keeps departments whose only projects are not active.
Group by d.id, d.name.
SELECT d.name AS department, SUM(CASE WHEN p.status = 'active' THEN 1 ELSE 0 END) AS active_projects FROM departments d LEFT JOIN projects p ON p.department_id = d.id GROUP BY d.id, d.name;
This query is meant to show each department as a percentage of total headcount, but COUNT(*) / total uses integer division, so every department reads 0. Fix the arithmetic so it returns the true percentage, rounded to one decimal place.
SELECT d.name AS department,
COUNT(*) / (SELECT COUNT(*) FROM employees) AS pct_of_company
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.id, d.name
;
Integer division drops the fraction. Multiply the top by 100.0 before dividing.
Wrap the result in ROUND(..., 1).
The denominator stays (SELECT COUNT(*) FROM employees) for the whole-company total.
SELECT d.name AS department, ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM employees), 1) AS pct_of_company FROM employees e JOIN departments d ON e.department_id = d.id GROUP BY d.id, d.name;
Show each department's payroll as a percentage of the whole company's payroll. Divide the department's SUM(salary) by the company total from a subquery, guard against integer division, and round to one decimal place. Call the column payroll_share_pct.
SELECT d.name AS department, -- department payroll as a percentage of the company total, rounded to 1 decimal AS payroll_share_pct FROM departments d JOIN employees e ON e.department_id = d.id GROUP BY d.id, d.name;
The numerator is SUM(e.salary); the denominator is (SELECT SUM(salary) FROM employees).
Multiply the numerator by 100.0 so the division keeps the fraction.
Wrap everything in ROUND(..., 1).
SELECT d.name AS department, ROUND(SUM(e.salary) * 100.0 / (SELECT SUM(salary) FROM employees), 1) AS payroll_share_pct FROM departments d JOIN employees e ON e.department_id = d.id GROUP BY d.id, d.name;
A query computes COUNT(*) / (SELECT COUNT(*) FROM employees) and every department shows 0. What is wrong?
Both operands are integers, so SQLite divides as integers and drops the remainder, giving 0. Forcing floating-point arithmetic with a 100.0 multiplier keeps the fraction and yields the real percentage.
Recap
- A KPI is one aggregate —
COUNT,SUM,AVG— pointed at a question a reader cares about; bare for company-wide, grouped for per-team. - Ratios need floating-point arithmetic: multiply by
100.0before dividing, or integer division silently returns zero. SUM(CASE WHEN ... THEN 1 ELSE 0 END)counts only the rows meeting a condition, without filtering the others away.COUNT(*)counts rows;COUNT(col)skips NULLs;AVGignores NULLs;COALESCE(SUM(x), 0)keeps a blank from showing as empty.- Do not fabricate a KPI from data that does not support it — name the proxy honestly.
Next you will lay these KPIs out over time, grouping by month and year and comparing cohorts hired in different periods.