Aggregate Functions

What Is an Aggregate Function?

An aggregate function takes many rows as input and collapses them into a single value. Where a scalar function (like UPPER()) transforms one row at a time, an aggregate function looks across a whole set of rows and produces one summary number, date, or string.

The five core aggregate functions in standard SQL are:

This chapter looks at each of these individually, applied to an entire table (no GROUP BY yet — that's Chapter 15, where aggregates are computed per-group instead of per-table).

Running Example

Every example in this chapter uses one orders table:

sql
CREATE TABLE orders (
  id            INT PRIMARY KEY,
  customer_name VARCHAR(50),
  amount        DECIMAL(10,2),
  order_date    DATE,
  status        VARCHAR(20)
);

INSERT INTO orders VALUES
  (1, 'Alice',   250.00, '2024-01-05', 'completed'),
  (2, 'Bob',     NULL,   '2024-01-07', 'pending'),
  (3, 'Alice',   400.00, '2024-02-10', 'completed'),
  (4, 'Charlie', 150.00, '2024-02-15', 'cancelled'),
  (5, 'Bob',     300.00, '2024-03-01', 'completed');

Row 2 (Bob) has a NULL amount — this matters a lot for what follows.

A Quick Look at All Five Together

sql
SELECT COUNT(*)      AS total_rows,
       COUNT(amount) AS priced_rows,
       SUM(amount)   AS total_amount,
       AVG(amount)   AS avg_amount,
       MIN(amount)   AS smallest,
       MAX(amount)   AS largest
FROM orders;

Result:

Notice total_rows (5) and priced_rows (4) differ — Bob's NULL amount is not counted by COUNT(amount).

The Golden Rule: Aggregates Ignore NULLs

SUM, AVG, MIN, MAX, and COUNT(column) all skip `NULL` values when doing their calculation. A NULL is treated as "unknown/absent," not as zero. Only COUNT(*) is different — it counts rows regardless of whether any column is NULL, because it isn't looking at a particular column's value at all.

This is why AVG(amount) above is 275.00 (1100 / 4), not 220.00 (1100 / 5). If NULL were treated as zero, the average would be pulled down — SQL deliberately avoids that.

Aggregates Return One Row

Without GROUP BY, an aggregate query collapses the entire result set into a single output row, no matter how many rows fed into it. You cannot mix an aggregate with a plain (non-aggregated, non-grouped) column in the same SELECT — SELECT customer_name, SUM(amount) FROM orders without a GROUP BY is invalid in standard SQL (some dialects like SQLite/MySQL will silently pick an arbitrary row, which is a trap, not a feature). Chapter 15 covers the correct way to mix a grouping column with aggregates.

Key Takeaways / Interview Q&A

Q: What's the difference between a scalar function and an aggregate function? A: A scalar function operates on one row's value and returns one value per row (e.g. UPPER(name)). An aggregate function operates across many rows and returns one value for the whole set (or group).

Q: Do aggregate functions count NULLs? A: No — except COUNT(*), every aggregate function ignores NULL values in its input column.

Q: Can `SUM()` return NULL? A: Yes — if every value in the column is NULL (or the table/group has zero rows), SUM() returns NULL, not 0.

Q: Name the five core aggregate functions. A: COUNT, SUM, AVG, MIN, MAX.