Handling Missing Values

COALESCE returns the first value that is not null. COALESCE(copies, 0) gives the number where there is one and 0 where there is not.

That is often what you want for display and often wrong for analysis. Treating unknown as zero pulls every average down, and the result looks entirely reasonable. Decide deliberately, exactly as in the cleaning module of APIs & Datasets, and say which you chose.

Try it

Work out which average you would publish, and what sentence you would write next to it.

loading editor…

real SQLite, in your browser

Two averages over the same column, both defensible, and meaningfully different. Which one is right depends on what a missing value means here.

◈ Graded Query

Return the title of every book whose number of copies is unknown, alongside the text 'unknown' as a second column. One row per such book, ordered by title.

Checked against the rows your query returns. 1 visible and 3 hidden checks, some against data you cannot see.

loading editor…

Mark this lesson complete

Signed in, your progress follows you to every device.

Next: Practice Test →

← Back