SQL-01-006-P · Primer — Aggregate Functions

The idea in one sentence

An aggregate function takes a whole column of many rows and collapses it into a single answer — how many, the total, the average, the smallest, the largest — so instead of scrolling through a thousand payments you ask one question and get one number back.

Why it exists

A plain SELECT gives you rows: one line out for every line that matches. That's perfect when you want to see the data, but most real questions aren't about individual rows at all. "How many orders did we get today?" "What's our total revenue?" "What's the average review score?" "What's the cheapest item we sell?" None of those want a list — they want a summary, a single value that stands in for the entire set.

Without aggregates you'd have to pull every row into your application and add them up in Python or JavaScript by hand — dragging thousands of rows across the network just to reduce them to one number. That's slow, wasteful, and easy to get wrong. Aggregate functions push that reduction down into the database, right next to the data, where the engine can do it in one optimized pass. You send a tiny question, the database does the heavy counting, and only the answer travels back.

That's the whole point of the five core aggregates — COUNT, SUM, AVG, MIN, and MAX. They turn "give me the rows" into "give me the story the rows tell."

How it works

An aggregate function sits in the SELECT list and, instead of reading one row, it reads every row the query would have returned and folds them into one value. COUNT(*) counts rows; SUM(col) adds a numeric column; AVG(col) averages it; MIN(col) and MAX(col) find the extremes (and MIN/MAX even work on text and dates, giving you alphabetical or chronological first/last). When there's no GROUP BY, the aggregate treats the whole table as one big group, so a query with only aggregates always returns exactly one row. The one rule to memorize: every aggregate except COUNT(*) skips NULL — nulls are quietly ignored, never counted, summed, or averaged. That single behavior explains most surprises, especially with AVG, where the null count changes the denominator.

-- Five payments; two have no tip recorded (NULL)
CREATE TABLE payments (id INTEGER, customer TEXT, amount REAL, tip REAL);
INSERT INTO payments VALUES
  (1, 'Ada',   40.00,  6.00),
  (2, 'Ben',   12.50,  NULL),
  (3, 'Ada',  100.00, 15.00),
  (4, 'Cleo',  25.00,  3.00),
  (5, 'Ben',   60.00,  NULL);

SELECT
  COUNT(*)              AS num_rows,     -- counts rows, NULLs included
  COUNT(tip)            AS tips_given,   -- counts non-NULL tips only
  SUM(amount)           AS total,
  ROUND(AVG(amount), 2) AS avg_amount,
  MIN(amount)           AS smallest,
  MAX(amount)           AS largest
FROM payments;

-- num_rows | tips_given | total | avg_amount | smallest | largest
-- ---------+------------+-------+------------+----------+--------
--    5     |     3      | 237.5 |   47.5     |   12.5   |  100.0

The one gotcha

COUNT(*) and COUNT(column) are not the same thing, and the difference is NULL. COUNT(*) counts rows — every row, no matter what's in it. COUNT(column) counts only the rows where that column is not null. In the example above, COUNT(*) is 5 but COUNT(tip) is 3, because two payments have a null tip. This trips people up constantly: someone writes AVG(tip) expecting the average tip "per payment," but AVG sums the three real tips (6 + 15 + 3 = 24) and divides by three, not five — giving 8.00, not 4.80. If you meant "average tip counting no-tip as zero," the nulls have to become zeros first (e.g. AVG(COALESCE(tip, 0))). And one more edge to file away: SUM and AVG over an empty set (or a column that's entirely null) return NULL, not 0. Whenever an aggregate result looks too high, too low, or mysteriously blank, your first suspect is always a null you forgot was there.

See it — how the engine folds many rows into one

flowchart TD
    A["payments table<br/>(5 rows)"] --> B{"Read each row<br/>one by one"}
    B --> C["COUNT: +1 per row<br/>(NULLs count)"]
    B --> D["SUM/AVG: skip NULL,<br/>add the rest"]
    B --> E["MIN/MAX: track<br/>smallest & largest"]
    C --> F["Fold into ONE row"]
    D --> F
    E --> F
    F --> G["1 summary row<br/>out"]

The one-line takeaway

Aggregate functions reduce many rows to a single summary value in one pass inside the database — just remember that every aggregate but COUNT(*) ignores NULL.

Where you'll meet it

You'll meet aggregates within your first hour of real SQL: every dashboard number — total sales, active users, average rating — is an aggregate underneath. You'll reach for them to sanity-check data ("does COUNT(*) match what the app claims?"), to build reports, and as the foundation for the very next thing you learn, GROUP BY, which just runs these same five functions once per group instead of once for the whole table. In interviews they show up early and often: "count the rows," "find the highest-paid employee," "what's the average order value?" — and the sharp follow-up is always some version of "what happens to the nulls?" Get comfortable narrating that COUNT(*) includes every row while SUM, AVG, MIN, MAX, and COUNT(col) all step around NULL, and you'll answer the whole family of questions with confidence.

Named concepts: COUNT, SUM and AVG, MIN and MAX, aggregates over a whole table