SUM, AVG, MIN and MAX
The four value aggregates all ignore NULL, which is why AVG is SUM divided by the count of non-NULL values, not by COUNT(*).
SUM([DISTINCT] expr) | AVG([DISTINCT] expr) | MIN(expr) | MAX(expr)SUM(), AVG(), MIN() and MAX() reduce a group of values to one value. They all ignore NULL, and that single rule explains nearly everything surprising about them.
NULL and the empty group
NULLs are not zeros and are not counted. So AVG(x) is SUM(x) / COUNT(x) — divided by the number of non-NULL values — and not SUM(x) / COUNT(*). If half your rows have a NULL discount, AVG(discount) is the average of the half that has one. If you want the NULLs to count as zero, say so: AVG(COALESCE(discount, 0)).
When no rows match, all four return NULL — only COUNT returns 0. A SUM feeding a calculation or a NOT NULL column therefore wants COALESCE(SUM(amount), 0).
Types and the temporal trap
With exact-value arguments (integer or DECIMAL) SUM and AVG return DECIMAL; with FLOAT or DOUBLE they return DOUBLE. Store money as DECIMAL so the total does not drift.
Neither works on temporal values: the manual states they convert the value to a number and lose non-numeric characters. Sum durations as seconds instead — SEC_TO_TIME(SUM(TIME_TO_SEC(t))).
MIN and MAX accept strings and dates. On ENUM and SET they compare by string value, not by the declared position — which is not how ORDER BY sorts the same column.
Example
-- Monthly revenue summary. amount_cents is BIGINT, discount_pct is nullable.
SELECT
DATE_FORMAT(placed_at, '%Y-%m') AS month,
COALESCE(SUM(amount_cents), 0) AS revenue_cents, -- NULL if no rows, so coalesce
AVG(amount_cents) AS avg_order_cents, -- = SUM(amount)/COUNT(amount)
AVG(COALESCE(discount_pct, 0)) AS avg_discount, -- treat "no discount" as zero
MIN(placed_at) AS first_order,
MAX(placed_at) AS last_order
FROM orders
WHERE status = 'paid'
GROUP BY month
ORDER BY month;When to use it
- Total revenue per month, with zero rather than NULL for months with no sales.
- Average basket value where some orders have no discount recorded.
- Find each product's first and last sale date to spot dead stock.
- Total call or session duration stored in a TIME column.
More examples
AVG(x) and its longhand, side by side
Puts the definition on screen. The third column is what people think AVG does; it only matches the first two when the column has no NULLs. Run it on a nullable column to see how far apart they are before trusting an average in a report.
-- These two columns are always equal: AVG is shorthand for exactly this.
SELECT
AVG(discount_pct) AS avg_builtin,
SUM(discount_pct) / COUNT(discount_pct) AS avg_longhand, -- same value
SUM(discount_pct) / COUNT(*) AS avg_over_all_rows -- different, and usually wrong
FROM orders;Summing durations without losing the clock
The documented workaround for temporal aggregation. Use it any time you are totalling TIME columns; the equivalent for DATE spans is FROM_DAYS(SUM(TO_DAYS(date_col))).
-- SUM() on a TIME column converts to a number and loses the colons.
-- Convert to seconds, aggregate, convert back.
SELECT
agent_id,
SEC_TO_TIME(SUM(TIME_TO_SEC(call_length))) AS total_talk_time,
SEC_TO_TIME(AVG(TIME_TO_SEC(call_length))) AS avg_talk_time
FROM calls
WHERE started_at >= '2026-09-01'
GROUP BY agent_id;MIN and MAX to fetch the bounds of each group
MIN and MAX work on dates and strings, not only numbers, which makes them the usual way to get a group's first and last event. Note that they give you the boundary value, not the whole row it came from — for that you need a window function or a join back.
-- Per product: price range and the window of time it has sold in.
SELECT
oi.product_id,
MIN(oi.unit_price) AS cheapest_sold,
MAX(oi.unit_price) AS dearest_sold,
MIN(o.placed_at) AS first_sold_at,
MAX(o.placed_at) AS last_sold_at
FROM order_items AS oi
JOIN orders AS o ON o.id = oi.order_id
GROUP BY oi.product_id
HAVING MAX(o.placed_at) < CURRENT_DATE - INTERVAL 90 DAY; -- nothing sold in 90 days
Discussion