Aggregation changes the grain of a result. The source has one row per attempt; the report has one row per model. Write that sentence before choosing GROUP BY columns. Grouping by both model and attempt id would preserve the original grain and defeat the report.
Three counts answer different questions. COUNT(*) counts input rows. COUNT(score) counts non-NULL score values. COUNT(DISTINCT score) counts distinct non-NULL score values. Two equal measurements are still two measured attempts.
Follow the data
WHERE filters rows before grouping. HAVING filters the resulting groups. If the requirement is at least two attempts, use HAVING COUNT(*) >= 2. Filtering missing scores in WHERE first would remove evidence that an attempt happened and could cause a model to disappear from the report.
Conditional aggregation counts a subset without dropping the other rows: SUM(CASE WHEN score >= 80 THEN 1 ELSE 0 END). The CASE contributes zero for unknown comparisons, keeping missing scores out of the pass count while leaving them in attempts. A group with two missing scores has attempts 2, measured 0 and passed 0.
For an empty input, a grouped query produces no groups. An aggregate without GROUP BY instead produces one row: COUNT(*) is zero, while SUM and AVG of an empty set are NULL. Do not infer one behavior from the other. Integer arithmetic can also surprise you: use 1.0 * passed / attempts when a fractional rate is intended, and define the denominator-zero policy.
SQLite permits some bare selected columns outside GROUP BY. Avoid relying on the arbitrary row they may come from. Select group keys and aggregates so the result has a clear meaning and transfers to stricter SQL dialects.
Work a complete example
The example includes its own schema and data. Every Run creates a new SQLite database, so changing or rerunning it cannot change your other exercises.
CREATE TABLE scores(id INTEGER PRIMARY KEY, model TEXT NOT NULL, score INTEGER);
INSERT INTO scores VALUES (1,'a',80),(2,'a',NULL),(3,'b',99),(4,'c',79),(5,'c',81);
SELECT model, COUNT(*) AS attempts, COUNT(score) AS measured,
SUM(CASE WHEN score >= 80 THEN 1 ELSE 0 END) AS passed
FROM scores
GROUP BY model
HAVING COUNT(*) >= 2
ORDER BY model;Diagnose the common failure
Using COUNT(score) for support accidentally excludes all-missing groups. Using COUNT(DISTINCT score) for attempts removes repeated observations. Placing a minimum-support condition in WHERE cannot express a condition on the completed group; move it to HAVING.
Try a change before building
Add another model with exactly one score of 100. It should not appear in the report. Then add a second row with NULL score for that model. Predict all three counts before running the query.
Move into practice
Define the row grain, distinguish COUNT forms and filter groups after aggregation. Checks test the rules; bug hunts ask you to identify a faulty line before seeing its test evidence. The fill-in keeps most of a working statement, and the build asks for the full contract. Public and hidden fixtures vary inputs, preserve duplicate counts and distinguish NULL from text. SQL results appear as tables; predict-output checks use compact JSON rows, one row per line, with null for SQL NULL.
Use the Watch & read resources for the lecture and primary SQLite reference. Reading or watching alone does not grant mastery. These are SQLite exercises; PostgreSQL, MySQL and SQL Server differ in types, syntax, query plans and concurrency behavior.