A class report lists four pupils but the SQL average appears to use only three scores. Should the missing score count as zero? Not automatically. In ordinary SQL aggregation, AVG(score) ignores NULL entries but includes genuine numeric zeros. Mixing those concepts can change the result and unfairly change the meaning of a class report.
In a database, NULL means a value is missing, unknown or not applicable in that field; it is not the number 0. AVG() calculates the mean of the non-NULL numeric input values, while 0 is a valid numeric input. The right denominator depends on which records have measured values, and the right interpretation depends on why some records are missing.
- 0 means a recorded numerical zero, when that is genuinely what the field represents.
- NULL is an absence of a stored numeric value, not automatically a failed assessment.
- AVG(score) averages recorded non-NULL scores only.
- COUNT(score) counts non-NULL scores; COUNT(*) counts rows.
- COALESCE(score,0) explicitly substitutes zero for NULL, which is a policy decision, not a neutral cleanup.
- First ask: What does missing mean in this particular dataset?
An original fictional class-score example, checked by hand
Imagine a fictional classroom practice database with four anonymous records. Pupil A has a verified score of 80 points out of 100. Pupil B has a score field of NULL because the work has not yet been marked. Pupil C has a verified score of 0 points. Pupil D has a verified score of 100 points. There are four rows, but only three recorded numerical results.
Step 1: List the numbers that can actually be averaged: 80, 0 and 100. Their sum is 180 points. Step 2: Divide by the number of non-NULL scores, 3. The result is 180 ÷ 3 = 60 points out of 100 for the three marked records. It is not the average of four completed assessments, and must not be described that way.
If someone replaces B’s missing score with 0, the same data becomes 80, 0, 0, 100. That average is 180 ÷ 4 = 45 points. The 15-point difference is not a discovery about the children. It was created by a changed rule for representing missing records.
The query and what each calculation means
SELECT COUNT(*) AS all_rows,
COUNT(score) AS marked_scores,
AVG(score) AS average_marked_score,
AVG(COALESCE(score, 0)) AS average_if_missing_is_zero
FROM practice_results;For the four imaginary rows, the outputs are all_rows = 4, marked_scores = 3, average_marked_score = 60 and average_if_missing_is_zero = 45. The last column is deliberately labelled to expose its assumption. It is not preferable merely because it counts every row.
SQL implementations can return different numeric types or display precision depending on column type and database system. The arithmetic meaning here does not depend on whether the display prints 60, 60.0 or 60.0000. The underlying input status does.
Did You Know? COUNT(*) and COUNT(column) answer different questions
The asterisk in COUNT(*) describes all rows. COUNT(score) counts rows where score is not NULL. This is particularly useful in a school dashboard: it lets you show both how many records are in the dataset and how many have recorded marks. A single mean without a count can make a tiny dataset look more complete than it is.
PostgreSQL’s official aggregate-function documentation specifies how its aggregates treat non-NULL inputs. The same documentation distinguishes aggregates that return NULL for empty input sets from a computed zero, another distinction worth preserving.
How missing data changes a reader’s conclusion
Suppose the teacher writes ‘The class average is 60’. That is ambiguous unless the reader knows that the calculation covers three marked pupils, not all four. A stronger statement is: ‘The average of the three recorded marks is 60/100; one mark is unavailable.’ This reports the result and its denominator without pretending to know Pupil B’s outcome.
The same distinction appears in a science project with an absent temperature measurement, a library record with an uncounted loan, and a survey question that one respondent skipped. Missing measurements should not silently become zero observations. In some legitimate assessments, the rule may indeed assign zero for non-submission; however, that requires an explicit verified marking policy and a recorded reason, not a database programmer’s default assumption.
Diagnose the learner’s misconception
Ask pupils: ‘A student hasn’t sat a test, and another attempted it and scored zero. Do they have the same recorded result?’ If they say yes, diagnose a data-definition gap, not an arithmetic problem. Ask what the zero actually measures, whether the test took place, and how the system should represent unavailable data.
Next show the two totals: 180 points over three results versus 180 over four rows. If a learner mechanically selects the number of rows as the denominator, ask them to point to the fourth verified score. If none exists, they should explain the missingness instead of inventing its value.
Progressive practice: make the decision visible
Stage 1 — sort values by meaning
Give a list of five labels: 25, 0, NULL, 60 and NULL. Ask students to identify how many numeric values there are (3), their sum (85) and their average (85 ÷ 3 = 28.33…). This is a toy calculation, not a real grade.
Stage 2 — compare two deliberate assumptions
Now ask what happens if both NULLs are explicitly converted into zeros. The sum stays 85, but the denominator becomes five: 85 ÷ 5 = 17. The student must write a sentence explaining why these are different questions, not competing calculator answers.
Stage 3 — repair a misleading dashboard
Present a dashboard labelled ‘Average of all five pupils: 28.33’. The correct repair is not necessarily to change 28.33 to 17. A stronger repair may be to label it ‘Average of three recorded scores, with two unavailable’ and separately show the full enrolment count. The mark allocation policy should be checked with the authorised teacher before treating missing work as zero.
Stage 4 — transfer to independent SQL
SELECT COUNT(*) AS rows_seen,
COUNT(minutes_read) AS recorded_reading_times,
AVG(minutes_read) AS average_recorded_minutes
FROM reading_log;Ask students what a NULL reading time means and whether a genuine zero-minute reading session might exist. A good answer explains the recording method and the distinction between absent observation and observed zero, rather than blindly editing the query.
A more subtle issue: missing values may not be random
Even a mathematically correct mean of available scores can be unrepresentative. If pupils with incomplete work tend to differ systematically from those with marks, the observed mean may not describe the entire class. SQL cannot fix this through AVG() alone. Record the missing-data mechanism, check the measurement process and avoid treating the available records as a random sample without evidence.
Also separate two meanings of NULL: it can represent ‘not yet measured’, ‘not applicable’ or ‘unknown’. They are not always analytically interchangeable. A high-quality table may use a separate status field, such as pending, not-assessed or exempt, so staff can interpret the absence honestly.
What progress looks like for learners, parents and teachers
- Learners: independently distinguish 0 from NULL and explain the denominator without a prompt.
- Teachers: label recorded-score counts alongside aggregates; verify assessment rules before turning missing into zero.
- Parents: ask what the dashboard covers and whether a missing mark was treated as an unmarked item, an absence or a scored zero.
- Project builders: use explicit field definitions, preserve raw inputs and compare the query result with one hand-checked test case.
Frequently asked questions
Does AVG() include a zero?
Yes. Zero is an ordinary numeric value and belongs in the average if it is a genuine observation of the quantity being measured.
Does COUNT(*) give the same denominator as AVG(column)?
Only when every row considered by the aggregate has a non-NULL value in that column. Filtering, grouping and joins can also affect which rows enter the calculation.
Should every missing school mark be recorded as zero?
No. A zero may be appropriate under an established assessment rule, but a missing entry can also mean not yet marked, absent, exempt or unknown. The database should not silently decide a school’s marking policy.
What does AVG() return if every value is NULL?
In PostgreSQL and standard SQL aggregate semantics, there is no numerical average to report; the aggregate returns NULL, not 0. Check the specific database and query semantics, especially filters and groups.
Continue learning: data needs definitions before formulas
Compare the way survey respondents differ from all enrolled pupils and why Excel can ignore numeric-looking text. For broader learning, visit the Core Aim learning hub, Education Hub and Clementi teaching reference.
The core aim is to teach students that a correct formula can still produce a misleading sentence if its denominator and missing-data policy are hidden. Make the meaning visible; then let the calculation do its proper job.