You have built a report. It runs, and it looks plausible - exactly when it is most dangerous. A report that runs without error is not the same as a report that is correct. A silently dropped employee, a count inflated by a careless join, or a total that almost adds up will all ship unnoticed unless you actively look for them.
Validation is the discipline of proving a report is right before anyone acts on it. You trust a query not because it ran, but because you compared it to an independent truth and the two agreed. This lesson teaches three checks that catch almost every reporting bug. A completeness check asks whether every row made it in. An accuracy check asks whether the numbers are right. A consistency check asks whether the parts add up to the whole. Each one targets a different way a correct-looking query can quietly lie.
flowchart TD
B["Build the report"] --> C["Run three checks"]
C --> CO["Completeness - every row present"]
C --> AC["Accuracy - numbers are right"]
C --> KN["Consistency - parts sum to whole"]
CO --> D{"All pass?"}
AC --> D
KN --> D
D -->|yes| S["Ship it"]
D -->|no| F["Fix query or data, re-check"]
style S fill:#0e7490,color:#fff
style F fill:#b45309,color:#fff
The completeness check: did every row make it in?
The simplest validation is also the most overlooked: count the rows your report is built on, and compare that count to a number you trust. The trustworthy number comes from the raw source table, before any join has had a chance to drop rows.
SQL gives you a subtle tool for this. COUNT(*) counts every row in a group, including rows where a column is NULL. COUNT(column) counts only the rows where that column holds a real value, silently skipping NULLs. Run both on the same table and the gap between the two is a built-in completeness check - every row in that gap is missing the exact value your report depends on.
SELECT COUNT(*) AS total_employees,
COUNT(department_id) AS assigned_to_a_dept
FROM employees;
flowchart LR A["15 employees"] --> J["INNER JOIN departments"] J --> K["14 rows with a match"] J -.->|"Omar - NULL department_id"| D["Dropped, no match"] style K fill:#0e7490,color:#fff style D fill:#b45309,color:#fff
The accuracy check: build the reference number
Accuracy means the figure on the page matches the figure in the data. The reliable way to confirm it is to compute the same metric a second, independent way and compare. If a report shows headcount per department, compute headcount directly from the employees table, grouped by department, and check the two agree.
The trap here is the one-to-many join. When you join departments to employees and then on to projects in the same query, a department with four employees and three projects produces twelve paired rows - every employee crossed with every project. An aggregate over that join counts twelve people where four work. Aggregate each table separately, then combine the results, so no join multiplies rows you never meant to count.
SELECT d.name AS department, COUNT(e.id) AS headcount FROM departments d LEFT JOIN employees e ON d.id = e.department_id GROUP BY d.name ORDER BY headcount DESC;
flowchart TD E["4 employees in Engineering"] --> J["JOIN to 3 projects"] J --> P["4 x 3 = 12 paired rows"] P --> C["COUNT returns 12 - inflated"] style C fill:#b45309,color:#fff style P fill:#1e293b,color:#fff
The consistency check: do the parts add up to the whole?
A report is usually several numbers that must fit together. The sum of every department's headcount should equal the company total. The sum of every department's payroll should equal the company payroll. When one section contradicts another, something is wrong - even if no single query looks broken.
The technique is reconciliation: compute a total directly, compute it again as the sum of its parts, and confirm the two match. When they do not, the gap points you straight at the section that is wrong. A common finding is a budget report that looks healthy per project but whose payroll dwarfs every project budget. The report was answering the wrong question, and only a cross-check between two tables reveals it.
SELECT d.name AS department, SUM(e.salary) AS payroll FROM departments d JOIN employees e ON e.department_id = d.id GROUP BY d.name ORDER BY payroll DESC;
When the check finds bad data
Not every discrepancy is a query bug. Sometimes the data itself is wrong, and the report was correct to surface it. A department with no city, a project with no budget, an employee with no department - these are data quality problems, and resolving them is part of iterating the report to completion.
The habit is to decide, for each finding, whether the row should be handled gracefully by the query or corrected at the source. If a blank city is a genuine gap, update the row. If an unassigned contractor is legitimate, change the query to report those rows explicitly instead of dropping them. Remediation is the final step of validation: you found the problem, now you close it and re-run the check.
Run a completeness check in one row: return the total number of employees and the number assigned to a department. The gap between them is your first quality signal.
SELECT
-- count all rows, then count non-NULL department_id values
FROM employees;
COUNT(*)counts every row;COUNT(department_id)skips rows where the column is NULL.Put both in one SELECT, each with its own alias.
SELECT COUNT(*) AS total, COUNT(department_id) AS assigned FROM employees;
Build the trustworthy headcount report: return each department's name and its employee count as headcount, highest first. Use a LEFT JOIN so a department with no employees would still appear.
SELECT d.name, COUNT(e.id) AS headcount FROM departments d -- join employees and group here ;
LEFT JOIN
employees e ON d.id = e.department_idkeeps every department.Group with
GROUP BY d.nameand sort withORDER BY headcount DESC.
SELECT d.name, COUNT(e.id) AS headcount FROM departments d LEFT JOIN employees e ON d.id = e.department_id GROUP BY d.name ORDER BY headcount DESC;
This headcount query joins departments to employees and to projects, so Engineering is reported as 12 instead of 4 - the project fan-out multiplied every employee. Remove the cause and return each department's true headcount, ordered by department name A to Z.
SELECT d.name, COUNT(e.id) 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 ORDER BY d.name;
Counting employees never needs the projects table - that join only inflates the count.
Drop the
JOIN projects pline entirely; keep the employees join and the GROUP BY.
SELECT d.name, COUNT(e.id) AS headcount FROM departments d LEFT JOIN employees e ON d.id = e.department_id GROUP BY d.name ORDER BY d.name;
A validation check found that the Research department has no city recorded - it shows as a blank on the report. This is a genuine data gap, so remediate it: set Research's city to 'Trondheim'.
UPDATE departments -- set the city for Research ;
UPDATE departments SET city = 'Trondheim'changes the value.Restrict it with
WHERE name = 'Research'so only that one row changes.
UPDATE departments SET city = 'Trondheim' WHERE name = 'Research';
Reconcile payroll against budget. Return the name and total payroll (SUM of salary) of every department whose payroll exceeds its total project budget, ordered by department name A to Z. This cross-check exposes departments where people cost more than the projects fund - a question the per-project report alone never asks.
SELECT d.name, SUM(e.salary) AS payroll FROM departments d JOIN employees e ON e.department_id = d.id GROUP BY d.id, d.name -- keep only departments whose payroll exceeds their total project budget ;
Compute each department's project budget with a subquery:
(SELECT COALESCE(SUM(p.budget), 0) FROM projects p WHERE p.department_id = d.id).Filter groups with
HAVING SUM(e.salary) > (...)- HAVING, not WHERE, because you are filtering an aggregate.
SELECT d.name, SUM(e.salary) AS payroll FROM departments d JOIN employees e ON e.department_id = d.id GROUP BY d.id, d.name HAVING SUM(e.salary) > (SELECT COALESCE(SUM(p.budget), 0) FROM projects p WHERE p.department_id = d.id) ORDER BY d.name;
Recap
- A report that runs is not a report that is correct. Prove it with checks.
- Completeness: compare
COUNT(*)toCOUNT(column)- the gap is the rows missing a value. INNER JOINsilently drops rows whose join key is NULL. Suspect it whenever a count is low.- Accuracy: compute each metric two independent ways and confirm they agree.
- A one-to-many join inflates counts. Aggregate each table separately, then combine.
- Consistency: the sum of the parts must equal the whole. Reconcile across sections with
HAVINGand subqueries. - When the data itself is wrong, decide whether to fix the row or handle it in the query.
Next you will take this validated, accurate report and make it fast and readable - optimising the queries and presenting the findings to a non-technical audience.