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;