Suppose a regression summary says there are two failed attempts. That number may be correct while the conclusion that two tests are broken is wrong. The attempts could be retries of one test, and an infrastructure error might have been excluded. SQL makes the counting rule explicit, but only after you define the question. State the population, status categories, grouping dimensions, and time or build scope before writing a query. Ask an assistant to explain those choices along with any proposed SQL.
SELECT chooses output columns. FROM names the records to read. WHERE keeps matching rows. GROUP BY collects rows sharing a key so aggregates such as COUNT can summarize each group. ORDER BY defines display order; without it, do not rely on the order you happen to see. JOIN combines related tables. Learn these pieces with a small inspectable example before using a generated query against a large engineering dataset.
Synthetic runs used in the next lab:
R1 B1 reset_smoke PASS
R2 B1 fifo_stress FAIL
R3 B1 fifo_stress FAIL
R4 B2 reset_smoke PASS
R5 B2 fifo_stress ERROR
SELECT build_id, COUNT(*) AS failed_attempts
FROM runs
WHERE status = 'FAIL'
GROUP BY build_id
ORDER BY build_id;
Expected rows by inspection: B1 | 2
B2 is absent because it has no rows matching FAIL.That absence is useful for a list of builds with failures. It is unsuitable for a dashboard that must show every build. Start from builds and use a LEFT JOIN when zero-attempt builds matter. Keep a status predicate inside the join condition when the question is how many failures each build has, including zero. Putting a child-status filter in WHERE can remove the preserved rows and defeat the purpose of the outer join.
SELECT b.build_id, COUNT(r.run_id) AS failed_attempts
FROM builds AS b
LEFT JOIN runs AS r
ON r.build_id = b.build_id AND r.status = 'FAIL'
GROUP BY b.build_id
ORDER BY b.build_id;
Expected: B1 | 2; B2 | 0; B3 | 0A failure fraction also needs a denominator. Here two FAIL attempts divided by all five attempts is 2/5. Two FAIL attempts divided by the four attempts with PASS or FAIL status is 2/4. Both calculations can be described precisely, but they answer different questions. Report ERROR separately and state which fraction you use. A build with no attempts has an undefined attempt failure fraction; displaying zero percent would imply evidence that does not exist.
One distinct test name, fifo_stress, has FAIL status; two attempts have FAIL status. Use COUNT(DISTINCT test_name) for the first question and COUNT(*) on the filtered runs table for the second. Neither count is a count of confirmed design bugs.
For a query accepting a status value, bind it as a parameter rather than inserting it into SQL text. Parameters represent values, not arbitrary table names or SQL clauses. A service accepting user-selected columns still needs an allowlist. In a real read-only workflow, use a constrained connection, fixed reviewed queries, and bounded result sizes. A natural-language request is never a reason to grant an assistant write access to the regression database.
These examples teach SQLite syntax, not every database dialect. Missing records, stale imports, and inconsistent status mappings can still produce misleading results. Reference: SQLite SELECT and aggregate functions, consulted 2026-10-18. The complete Python parameter-binding example follows in the lab.