97. COUNT(*) vs COUNT(column) and AVG over NULLs
MediumNULL HandlingCOUNT(*)COUNT(col)AVG ignores NULL
Aggregates treat NULL differently depending on form. COUNT(*) counts rows; COUNT(col) counts only rows where col is not NULL; and AVG(col) averages only the non-NULL values (it divides by the non-NULL count, not the row count).
Task: over the whole reviews table return a single row with total_rows (= COUNT(*)), rated_rows (= COUNT(score)), and avg_score (= AVG(score) rounded to 2 decimals). This single-row aggregate demonstrates how the three differ when score contains NULLs.
Output columns, in order: total_rows, rated_rows, avg_score.
Tables
Loading schema…
Expected outputrow order doesn't matter
Computing expected output…
This is the result for the example data above. On Submit your query is graded against this example plus 4 hidden edge cases — 5test cases in all. A sloppy query that only fits the example won't pass.
SQL· SQLite · runs in your browser
⌘/Ctrl+↵ run · ⇧⌘/Ctrl+↵ submit
Run a query to see its output here.