Aggregates, NULL and Aggregate Expressions

Concept

An aggregate does not have to take a plain column. It takes an expression, evaluated per row before the aggregation happens. That one fact unlocks weighted totals, conditional counting and most of what people reach for a spreadsheet to do.

Syntax

SUM(expression)
COUNT(CASE WHEN condition THEN 1 END)
AVG(CASE WHEN condition THEN column END)

Example

-- A weighted total: line value is quantity times price
SELECT SUM(quantity * unit_price) AS gross_revenue,
       SUM(quantity)              AS units_sold,
       ROUND(SUM(quantity * unit_price) / SUM(quantity), 2) AS weighted_avg_price
FROM   order_items;

-- Conditional aggregation: several answers from one pass over the table
SELECT COUNT(*)                                                AS all_orders,
       COUNT(CASE WHEN status = 'shipped'   THEN 1 END)        AS shipped,
       COUNT(CASE WHEN status = 'cancelled' THEN 1 END)        AS cancelled,
       SUM(CASE WHEN status = 'shipped' THEN total ELSE 0 END) AS shipped_value,
       ROUND(100.0 * COUNT(CASE WHEN status = 'cancelled' THEN 1 END) / COUNT(*), 1)
                                                               AS cancel_rate_pct
FROM   orders;

Explanation

COUNT(CASE WHEN condition THEN 1 END) is the workhorse. The CASE returns 1 for matching rows and NULL for the rest - and because COUNT(expr) ignores NULL, only the matching rows are counted. Deliberately omitting ELSE is the trick; adding ELSE 0 would count everything.

With SUM it is the opposite: SUM(CASE ... THEN total ELSE 0 END) needs the ELSE 0 only for readability, since SUM ignores NULL anyway.

100.0 * rather than 100 * forces decimal arithmetic, so the percentage does not get truncated to a whole number in dialects that do integer division.

Weighted averages

-- Wrong: the plain average of unit prices ignores how many were sold
SELECT AVG(unit_price) FROM order_items;

-- Right: total value divided by total quantity
SELECT SUM(quantity * unit_price) / SUM(quantity) AS weighted_avg FROM order_items;

Important rules

• The expression is evaluated per row, then the aggregate runs over the results.
• COUNT(expr) counts non NULL results, which is what makes conditional counting work.
• SUM of no rows is NULL, not zero. Wrap it in COALESCE for display.
• You cannot nest aggregates. MAX(SUM(total)) needs a subquery or a CTE providing the inner grouping.
• DISTINCT may be combined with an aggregate: SUM(DISTINCT total) is legal, and almost always a mistake.

Common mistakes

• Writing COUNT(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) and getting the total row count, because 0 is not NULL.
• Reporting an unweighted AVG(unit_price) as the average selling price.
• Computing a percentage with integer arithmetic and always getting 0 or 100.
• Running one query per status when a single conditional aggregation would answer all of them in one scan.

Best practices

• Prefer conditional aggregation to several near identical queries - one pass over the data instead of five.
• Omit ELSE in COUNT(CASE ...), and include ELSE 0 in SUM(CASE ...).
• Multiply by 100.0 when computing percentages.
• Name aggregate columns for what they measure, including the filter: shipped_order_value, not total.

Practice

1. In one query, return the number of active and inactive employees and the payroll for each group.
2. Compute the percentage of orders that were cancelled, to one decimal place.
3. Explain why the weighted average price differs from AVG(unit_price) on the sample data.