SQL Averages Are Lying to You: The NULL Problem Nobody Talks About

type

status

date

slug

summary

The NULL Problem That Nobody Explains

When you learned about AVG(), you probably thought it worked like this:

Take all the numbers in a column

Add them up

Divide by the number of rows

Return the average

That's not what happens.

Here's what AVG() actually does:

Take all the non-NULL numbers in a column

Add them up

Divide by the count of non-NULL values (not total rows)

Return the average, completely ignoring NULLs

Let me show you why this matters with a real example.

Our employee table:

Now let's run three different queries on this exact same data:

Query 1: Standard AVG() - ignores NULLs

Query 2: Treating NULL as zero

Query 3: Average only full-time employees

Same data. Same function. Three completely different results: $70,000, $42,000, and $70,000.

Which one is "correct"? It depends entirely on what business question you're trying to answer. Are you calculating average salary per full-time employee? Average cost per employee including contractors? Average among employees with recorded salaries?

This is where most SQL tutorials stop—and where most analytics projects go wrong.

Why This Behavior Exists (And When It's Helpful)

SQL's design decision to exclude NULLs from AVG() isn't a bug. It's intentional. SQL treats NULL as "unknown" or "not applicable"—fundamentally different from zero.

Let me show you three scenarios where excluding NULLs actually makes perfect sense.

Example 1: Product ratings

Correct interpretation: Average rating of products that have been rated

Wrong interpretation: Treating unrated products as 0-star products (which would tank your ratings unfairly)

Example 2: Optional survey responses

Correct interpretation: Average score among customers who responded

Wrong interpretation: Counting non-responses as 0/10 dissatisfaction (which misrepresents customer sentiment)

Example 3: Performance bonuses

Correct interpretation: Average bonus among employees who received one

Wrong interpretation: Average bonus per employee (which should include all employees, most with zero)

The principle is simple: NULL means "this doesn't apply" or "we don't know"—it's not the same as "this is zero."

When a product hasn't been rated, that's different from a 0-star rating. When a customer doesn't answer a question, that's different from answering "0." When an employee doesn't get a bonus, that might mean NULL (not eligible) or it might mean 0 (eligible but earned nothing).

This is where context matters.

When NULL Exclusion Causes Wrong Results

Now for the dangerous part: scenarios where AVG() excluding NULLs gives you technically correct math but completely wrong business insights.

Scenario 1: Commission-based sales team

Your sales team works on commission. Most reps make sales every month. A few reps had a bad month and made zero sales—recorded as NULL in your database.

Your report to management: "Average commission is $4,500"

Reality: You're only counting successful reps, hiding the full picture

Better approach:

Scenario 2: Website engagement metrics

You're analyzing how long users spend on your site.

If NULL means "user bounced immediately," you're only measuring engaged users—not the bounce rate impact. Your analytics dashboard shows great engagement numbers, but you're missing the users who left right away.

Better approach:

Scenario 3: Student test scores

Students take a test. Some were absent (recorded as NULL).

For grade calculations, absences might need to count as zero. But your query is only showing the average among students who showed up.

The danger is subtle but critical: AVG() gives you mathematically correct results based on available data, but potentially misleading business insights if you don't understand what your NULLs represent.

Three Practical Solutions

Here's how to handle NULL values correctly in your averages:

Solution 1: Explicitly treat NULL as zero with COALESCE

When to use: When NULL logically means "zero" for your business case—like zero sales, zero commission, zero clicks, or zero bonus earned.

Solution 2: Filter out NULLs explicitly with WHERE

When to use: When you want to be crystal clear about excluding NULLs, or when you're combining multiple filters. This makes your intent explicit in the query.

Solution 3: Use CASE to handle NULLs differently

When to use: When different NULL scenarios need different handling. Maybe contractors with NULL salary should count as zero, but new hires with NULL should use a default starting salary.

Pro tip: Always check NULL counts first

Before calculating any average, run this diagnostic query:

This shows you what data you're working with before making decisions about how to handle NULLs.

Decision Framework: Which Average Do You Need?

Here's a quick decision tree for choosing the right approach:

Ask yourself: "What does NULL mean in my data?"

→ NULL = "Doesn't apply" (unrated products, optional bonuses)

Use standard AVG(column) — exclude NULLs ✓

→ NULL = "Zero" (no sales, zero clicks, no bonus earned)

Use AVG(COALESCE(column, 0)) — treat as zero ✓

→ NULL = "Missing/Unknown data" (data entry error, integration failure)

Fix the data first, then calculate ✓

Or exclude with WHERE column IS NOT NULL ✓

→ NULL = "Different scenarios" (contractors vs employees, absent vs failed)

Use CASE statement for conditional logic ✓

The Bottom Line

SQL's AVG() function excludes NULL values by design—it's not a bug, it's a feature. But it's a feature you must understand, because the same dataset can produce wildly different averages depending on how you handle NULLs.

Remember these three things:

AVG(column) automatically excludes NULLs from both the sum and the count

Use COALESCE(column, 0) when NULL logically means zero in your business context

Always check your NULL counts before trusting your averages

The "right" average depends on your business question, not just the SQL syntax. Next time you write AVG(), pause and ask yourself: "What do my NULLs represent?" Your analytics accuracy depends on getting this right.

Try this right now: Run the side-by-side comparison below on your own database. You might be surprised by what you find—and how many "averages" you've been calculating wrong.

Because in SQL, the question isn't just "What's the average?"—it's "What are you trying to measure?"

SEO Metadata

Primary Keyword: SQL AVG NULL

Secondary Keywords:

SQL AVG function NULL values

SQL average ignores NULL

SQL AVG exclude NULL

COALESCE SQL average

SQL aggregate functions NULL handling

SQL AVG wrong results

SQL NULL behavior

URL Slug: sql-avg-null-problem-explained

Meta Description:

SQL's AVG() function excludes NULL values by design—causing beginners to get technically correct results but wrong business insights. Learn how AVG() handles NULLs, three practical solutions (COALESCE, WHERE, CASE), and a decision framework to choose the right approach for your analytics.

Medium Tags: SQL, Database, Programming, Data Analysis, Beginner Tutorial

Medium Subtitle: Why your salary averages might be wrong (and three ways to fix them)

Word Count: ~1,400 words

Reading Time: 7 minutes

Target Audience: SQL beginners, junior data analysts, developers learning database fundamentals