SQL AVG NULL Behavior — Why Your LTV Report Was 23% Off

Every real application — whether it's a shopping site showing 'Average rating: 4.7 stars', a bank dashboard showing 'Total spent this month: $1,240', or an HR tool reporting 'Headcount: 342 employees' — uses aggregate functions behind the scenes. They're the bridge between raw row-level data and the summary numbers that drive business decisions.

The rules are simple until NULL gets involved. COUNT(*) and COUNT(column_name) behave completely differently. AVG silently ignores NULL values. GROUP BY requires every non-aggregated column in your SELECT. These aren't obscure edge cases — they're the cause of a surprisingly large percentage of wrong report numbers in production.

What Aggregate Functions Actually Do (And Why They Exist)

A regular SQL query like SELECT name FROM employees returns one row per employee — every single row that matches your WHERE clause. An aggregate function changes that contract entirely. Instead of 'give me all the rows', it says 'give me one summarised value across all the rows.' This is what makes them aggregate — they combine many rows into one.

Five aggregate functions cover the vast majority of real-world analytics: COUNT (how many rows), SUM (total value), AVG (mean value), MIN (smallest value), MAX (largest value). All five share a critical characteristic: they handle NULL values by ignoring them — except COUNT(*), which is the one exception.

-- Setup: a simple orders table
CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT,
    total       DECIMAL(8,2),
    status      VARCHAR(20)
);

INSERT INTO orders VALUES
    (1, 101, 49.99,  'completed'),
    (2, 102, 120.00, 'completed'),
    (3, 101, NULL,   'pending'),   -- NULL total: not yet charged
    (4, 103, 75.50,  'completed'),
    (5, 102, 30.00,  'refunded');

-- Five aggregates on the same column
SELECT
    COUNT(*)            AS total_rows,       -- 5: counts every row
    COUNT(total)        AS rows_with_total,  -- 4: skips the NULL row
    SUM(total)          AS revenue,          -- 275.49: skips NULL
    AVG(total)          AS avg_order,        -- 68.87: 275.49/4, not /5!
    MIN(total)          AS smallest,
    MAX(total)          AS largest
FROM orders;

COUNT, SUM and AVG — The Most-Used Trio

COUNT comes in two forms with very different behavior. COUNT(*) counts every row in the result set, including rows with NULL values in every column. COUNT(column_name) counts only rows where that specific column is not NULL. This distinction bites constantly in production.

SUM adds all non-NULL values. NULL rows contribute nothing. SUM of a column with all NULLs returns NULL, not zero — this surprises developers who expect 0. Use COALESCE(SUM(column), 0) when zero is the correct return for an all-NULL column.

AVG computes the mean of non-NULL values only. It's mathematically equivalent to SUM(column) / COUNT(column) — both numerator and denominator exclude NULLs.

-- Demonstrating the COUNT(*) vs COUNT(column) difference
SELECT
    COUNT(*)           AS all_orders,
    COUNT(total)       AS charged_orders,
    COUNT(*) - COUNT(total) AS pending_not_charged,
    SUM(total)         AS total_revenue,
    COALESCE(SUM(total), 0) AS revenue_safe,  -- 0 if all NULLs
    AVG(total)         AS avg_charged,        -- excludes NULLs
    AVG(COALESCE(total, 0)) AS avg_all        -- includes NULLs as zero
FROM orders
WHERE status != 'refunded';

-- COUNT DISTINCT: unique customer count
SELECT
    COUNT(DISTINCT customer_id) AS unique_customers,
    COUNT(customer_id)          AS total_orders
FROM orders
WHERE status = 'completed';

MIN and MAX — Finding the Extremes in Your Data

MIN and MAX are the simplest aggregate functions to understand, and also the most underestimated. They don't just work on numbers — they work on dates, times, and text too. MIN on a date column gives the earliest date. MAX on a VARCHAR column gives the last value alphabetically. This makes them useful for pattern like 'first order date', 'most recent login', and 'last employee hired'.

-- MIN/MAX on dates: first and most recent order per customer
SELECT
    customer_id,
    COUNT(*)            AS order_count,
    MIN(created_at)     AS first_order_date,
    MAX(created_at)     AS most_recent_order,
    SUM(total)          AS lifetime_value,
    MAX(total)          AS largest_single_order
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
ORDER BY lifetime_value DESC;

GROUP BY — Where Aggregate Functions Become Truly Powerful

Everything so far has given us a single row summarising the whole table. GROUP BY changes that: instead of one grand total, it calculates a separate aggregate for each unique value in the grouping column. This is what produces 'revenue by category', 'orders per customer', 'signups per month'.

The rule is strict: every column in SELECT must either be in GROUP BY or wrapped in an aggregate function. You cannot SELECT a column that isn't in GROUP BY and isn't aggregated — it's undefined which row's value should appear for the group.

-- Revenue and order count by status
SELECT
    status,
    COUNT(*)         AS order_count,
    SUM(total)       AS total_revenue,
    AVG(total)       AS avg_order_value,
    MIN(total)       AS smallest_order,
    MAX(total)       AS largest_order
FROM orders
GROUP BY status
ORDER BY total_revenue DESC;

-- HAVING: only show statuses with 2 or more orders
SELECT
    status,
    COUNT(*) AS order_count,
    SUM(total) AS revenue
FROM orders
GROUP BY status
HAVING COUNT(*) >= 2  -- filter AFTER aggregation
ORDER BY revenue DESC;

HAVING — The WHERE Clause for Aggregated Data

You can't use WHERE on aggregate results. That's not a bug, it's a SQL evaluation order constraint. WHERE filters rows before aggregation happens. Once COUNT(), SUM(), or AVG() collapse your rows into a single value per group, WHERE has already finished its job.

HAVING is your escape hatch. It runs after GROUP BY and aggregation, letting you filter on the computed result. Think of it as WHERE for groups, not rows. If you want departments where average salary exceeds $80,000, HAVING AVG(salary) > 80000 is the only way. Writing that filter in WHERE would silently fail or return nonsense.

The catch: HAVING forces a full scan of all groups before filtering. On large datasets with many groups, this hurts. Use WHERE to eliminate rows early whenever possible. Push filtering down to WHERE before aggregation, then only use HAVING for post-aggregation conditions. Your query planner will thank you.

// io.thecodeforge — database tutorial

-- Find departments with above-average total payroll
SELECT 
    department_id,
    COUNT(employee_id) AS headcount,
    SUM(salary) AS total_payroll,
    ROUND(AVG(salary), 2) AS avg_salary
FROM employees
-- WHERE happens here, before aggregation
WHERE termination_date IS NULL
GROUP BY department_id
-- HAVING happens after aggregation
HAVING SUM(salary) > 500000
ORDER BY total_payroll DESC;

NULL Handling — The Silent Data Poison

Aggregate functions and NULL have a complicated relationship. COUNT(*) counts everything, including rows where every column is NULL. COUNT(column_name) skips NULLs entirely. SUM(), AVG(), MIN(), MAX() all silently discard NULL values before computing. Your average might be calculated over 80% of your data and you'd never know.

This becomes a production nightmare when NULLs represent missing data versus actual zeros. A NULL salary could mean "not yet entered" or "contractor with no salary." Either way, AVG(salary) treats both identically — it skips them. The result looks fine but misrepresents reality. Suddenly your quarterly compensation report shows an average that's 15% too high because entry-level positions had NULLs in the pipeline.

Mitigation: Always check NULL percentages before running aggregates. Use COUNT(*) vs COUNT(column) to gauge nullability. For critical reports, consider COALESCE() or explicit NULL handling. Better yet, enforce NOT NULL constraints at the schema level if NULLs aren't semantically valid. Database design debt shows up first in aggregate queries.

// io.thecodeforge — database tutorial

-- Spot NULL poisoning before it hits your aggregates
SELECT 
    COUNT(*) AS total_rows,
    COUNT(employee_id) AS ids_present,
    COUNT(salary) AS salaries_present,
    COUNT(*) - COUNT(salary) AS missing_salaries,
    ROUND(100.0 * COUNT(salary) / COUNT(*), 1) AS salary_completeness_pct,
    ROUND(AVG(salary), 2) AS naive_average,
    ROUND(AVG(COALESCE(salary, 0)), 2) AS zero_filled_average
FROM employees
WHERE department_id = 17;

Related Content

Understanding SQL aggregate functions is essential, but they don't exist in isolation. To build robust data pipelines, you should pair them with window functions like ROW_NUMBER() and RANK() for running totals without collapsing rows. JOINs are critical: aggregates often query multiple tables, so mastering INNER JOIN and LEFT JOIN ensures your SUM or COUNT reflects correct relationships. Subqueries let you nest aggregates — for instance, finding departments above average salary. Finally, indexes on grouped columns drastically improve performance; without them, large datasets turn aggregate queries into bottlenecks. Explore these topics to move beyond basic reporting into performant, analytic SQL.

// io.thecodeforge — database tutorial
// 25 lines max
WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT d.department_name, da.avg_salary
FROM departments d
JOIN dept_avg da ON d.id = da.department_id
WHERE da.avg_salary > (
    SELECT AVG(salary) FROM employees
);

Feedback

Your input directly shapes TheCodeForge.io's database content. Did the NULL handling section clarify how COUNT(*) differs from COUNT(column)? Was the HAVING example with real sales data intuitive, or did it need more edge cases? I want to know if the code snippets run cleanly in your environment — particularly the MIN/MAX on timestamps, which often trips up junior engineers. Drop a comment below or open an issue in our GitHub repo. Specific feedback like 'Show how AVG ignores NULLs in salary calculations' helps us refine future tutorials. Every response gets a direct reply from our senior engineering team. We build these guides for you, not for search engines.

// io.thecodeforge — database tutorial
// 25 lines max
SELECT 
    department_id,
    COUNT(*) AS total_rows,
    COUNT(salary) AS non_null_salaries,
    AVG(salary) AS avg_salary
FROM employees
WHERE department_id IN (10, 20)
GROUP BY department_id;

Conclusion

SQL aggregate functions transform raw rows into actionable insights — but only when you apply them with precision. We've covered the mechanics of COUNT, SUM, AVG, MIN, MAX, and the critical grouping duo GROUP BY and HAVING. The key lesson: aggregates collapse data, so always ask 'What am I losing?' when grouping. Beware NULLs: they silently exclude rows from AVG and SUM unless you use COALESCE. Start your next query by identifying the business question, then pick the aggregate that answers it directly. Run EXPLAIN ANALYZE on heavy aggregations. Master these tools, and you'll move from writing queries to designing data narratives.

// io.thecodeforge — database tutorial
// 25 lines max
SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    COUNT(DISTINCT customer_id) AS unique_buyers,
    ROUND(AVG(total_amount)::numeric, 2) AS avg_order_value
FROM orders
WHERE status = 'completed'
GROUP BY year
HAVING COUNT(*) > 100
ORDER BY year DESC;