Help improve SovranCode?

Google Analytics can measure anonymous usage after you choose Allow. Essential account and progress features work either way.

SovranCode
SQL: Query, Model, and Analyze Data Aggregate Functions
This device
Course contentsAggregate Functions · 32 topics

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsPlannedSelf joins and many-to-many dataPlannedSubqueries and correlated subqueriesPlannedUNION, INTERSECT, and EXCEPTPlanned

Designing reliable schemas

CREATE TABLE and schema designPlannedConstraints and data integrityPlannedNormalization and intentional denormalizationPlannedViews and materialized viewsPlanned

Writing and protecting data

INSERT, UPDATE, and DELETEPlannedUpserts and conflict handlingPlannedTransactions and savepointsPlannedIsolation, locks, and concurrencyPlanned

Performance and administration

Indexes and access pathsPlannedEXPLAIN and query plansPlannedQuery tuning patternsPlannedBackups, restores, and migrationsPlanned

Security and production workflow

Users, roles, and least privilegePlannedPreventing SQL injectionPlannedStored procedures, functions, and triggersPlannedProduction data projectPlanned
SQL: Query, Model, and Analyze Data12 complete · 20 planned

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsPlannedSelf joins and many-to-many dataPlannedSubqueries and correlated subqueriesPlannedUNION, INTERSECT, and EXCEPTPlanned

Designing reliable schemas

CREATE TABLE and schema designPlannedConstraints and data integrityPlannedNormalization and intentional denormalizationPlannedViews and materialized viewsPlanned

Writing and protecting data

INSERT, UPDATE, and DELETEPlannedUpserts and conflict handlingPlannedTransactions and savepointsPlannedIsolation, locks, and concurrencyPlanned

Performance and administration

Indexes and access pathsPlannedEXPLAIN and query plansPlannedQuery tuning patternsPlannedBackups, restores, and migrationsPlanned

Security and production workflow

Users, roles, and least privilegePlannedPreventing SQL injectionPlannedStored procedures, functions, and triggersPlannedProduction data projectPlanned
PREVIOUS LESSONText, Dates, Patterns, and Conditional Results
NEXT LESSONGROUP BY and HAVING
Reports, aggregation, and analytics · Lesson 09 125 min

Aggregate Functions

An aggregate turns several input rows into one answer. A count answers how many, a sum answers how much, and an average answers how much per known value. The SQL is short; the hard part is saying exactly which rows and values the number represents.

What you will leave with

You will be able to distinguish COUNT(*) from COUNT(column), calculate totals and averages over the intended rows, find extremes with MIN and MAX, explain how NULL and empty inputs behave, and decide when DISTINCT changes a metric.

One result row can summarize many input rows

PopulationDecide which rows qualify before calculating a metric.
MeasureChoose the aggregate that matches the question and its unit.
Missing valuesKnow which functions ignore NULL and what an empty input returns.
ReviewCompare the answer with source rows and a manual calculation.
SOURCE8 orders5 paid · 2 pending · 1 refunded

Two pending amounts are NULL; a refunded amount is zero.

FILTERWHERE status = 'paid'5 qualifying rows

Filtering happens before the aggregate reads the values.

RESULT12,000 centsSUM(amount_cents)

That paid-order total is one result value.

COUNT(*) counts rows

COUNT(*) counts every input row, regardless of which columns are NULL. This table has eight orders, so the result is eight. Without GROUP BY, an aggregate query returns one summary row for all qualifying input rows. The next lesson splits that population into groups.

SQL BROWSER RUNNER

Count every order

Run COUNT(*) over eight seeded orders.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

SELECT COUNT(*) AS order_count FROM orders;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Add WHERE status = 'pending' and predict the count. Then remove the filter.

COUNT(column) skips NULL values

COUNT(amount_cents) returns six because two pending orders have NULL amounts. The refunded zero still counts: zero is a value. COUNT(DISTINCT customer_id) counts four customers rather than eight order entries.

SQL BROWSER RUNNER

Compare rows, known amounts, and customers

See how the argument to COUNT changes what gets counted.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

SELECT COUNT(*) AS all_rows,
       COUNT(amount_cents) AS known_amounts,
       COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Replace customer_id with status inside COUNT(DISTINCT ...). Explain why the result is three.

SUM and AVG ignore NULL, but not zero

SUM(amount_cents) totals the six known amounts: 12,000 cents. AVG(amount_cents) divides by six, producing 2,000 cents. It does not divide by all eight rows. The zero refund contributes zero to the sum and one known value to the average denominator.

SQL BROWSER RUNNER

Calculate a total and mean

Compare sum and average over all known amounts.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

SELECT SUM(amount_cents) AS total_cents,
       AVG(amount_cents) AS average_cents
FROM orders;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Add COUNT(amount_cents), then verify 12000 / 6 = 2000.

Name the population in a report

A total over every status includes a zero refund and ignores unknown pending amounts. That may not be your revenue definition. State the status rule before naming a result revenue.

WHERE defines the aggregate population

The paid-only query has five input rows, a 12,000-cent sum, and a 2,400-cent average. Its total matches the all-status total because the other known amount is zero. Its average differs because that zero no longer affects the denominator. Equal totals can still describe different populations.

SQL BROWSER RUNNER

Summarize only paid orders

Filter rows first, then count and average paid amounts.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

SELECT COUNT(*) AS paid_orders,
       SUM(amount_cents) AS paid_cents,
       AVG(amount_cents) AS average_paid_cents
FROM orders WHERE status = 'paid';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Change the filter to refunded. Predict COUNT, SUM, and AVG for the single zero-amount row.

MIN and MAX find extremes among known values

MIN(amount_cents) is zero and MAX(amount_cents) is 4,000 over all orders. Both ignore null amounts. MIN(placed_on) and MAX(placed_on) return the first and last ISO dates. An extreme value alone does not identify the row it came from; selecting unrelated columns beside an aggregate is not a portable way to find its owner.

SQL BROWSER RUNNER

Find amount and date boundaries

Inspect the smallest and largest known values.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

SELECT MIN(amount_cents) AS smallest_known_amount,
       MAX(amount_cents) AS largest_known_amount,
       MIN(placed_on) AS first_order_date,
       MAX(placed_on) AS last_order_date
FROM orders;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Add WHERE status = 'paid'. Which minimum changes, and which maximum stays the same?

An empty input still produces one summary row

No order has status cancelled. In an ungrouped aggregate query, COUNT(*) returns zero while SUM, AVG, MIN, and MAX return NULL. If a dashboard deliberately displays zero for an empty sum, write COALESCE(SUM(amount_cents), 0) and make that choice visible.

SQL BROWSER RUNNER

Observe aggregates with no matches

Compare COUNT with SUM and AVG for an empty input.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

SELECT COUNT(*) AS matches,
       SUM(amount_cents) AS total_cents,
       AVG(amount_cents) AS average_cents,
       MIN(amount_cents) AS smallest_cents
FROM orders WHERE status = 'cancelled';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Add COALESCE around SUM and compare its zero display value with the original NULL.

DISTINCT changes the values being measured

COUNT(DISTINCT customer_id) answers how many customers appear, not how many orders they made. SUM(DISTINCT amount_cents) is legal, but it discards repeated equal amounts even when they came from separate valid orders. Do not use it to hide duplicate rows caused by a bad join.

SQL BROWSER RUNNER

Compare entries with unique values

Count order entries and unique customers, then inspect a distinct amount sum.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

SELECT COUNT(customer_id) AS customer_entries,
       COUNT(DISTINCT customer_id) AS unique_customers,
       SUM(DISTINCT amount_cents) AS sum_of_unique_amounts
FROM orders;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Change order 7's amount to 1000. Compare SUM(amount_cents) with SUM(DISTINCT amount_cents).

Conditional counts answer several questions at once

A CASE expression turns a true condition into one and other rows into zero. Summing those values counts paid and pending orders in one result row: five paid and two pending. GROUP BY is better when a report needs one row per status.

SQL BROWSER RUNNER

Count statuses in one summary

Use SUM and CASE to count paid and pending orders.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

SELECT COUNT(*) AS all_orders,
       SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
       SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders
FROM orders;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Add a refunded_orders expression and verify it returns one.

Common aggregate mistakes

COUNT(amount_cents) = all orders

NULL values omitted

Use COUNT(*) for rows; use COUNT(column) for known values.

AVG = SUM / COUNT(*)

Wrong denominator

AVG(column) divides by non-null values, including zero.

SUM(all statuses) = revenue

Undefined population

Filter to the business event the metric represents before naming the result.

SUM(DISTINCT amount_cents)

Lost legitimate repeats

Equal amounts can represent separate orders. DISTINCT works on values, not identity.

Independent lab: audit a paid-order snapshot

Build one September paid-order result with order count, paying customer count, total cents, average cents, and smallest and largest paid amounts. The starter returns five orders, four customers, 12,000 total cents, 2,400 average cents, 1,000 minimum, and 4,000 maximum. Move the lower date boundary to September 3 and predict every metric before running it.

SQL BROWSER RUNNER

Audit a six-metric paid-order report

Combine a half-open month filter with counts, totals, averages, and extremes.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  amount_cents INTEGER,
  placed_on TEXT NOT NULL
);

INSERT INTO orders (order_id, customer_id, status, amount_cents, placed_on) VALUES
  (1, 10, 'paid', 1000, '2026-09-01'),
  (2, 11, 'paid', 2500, '2026-09-02'),
  (3, 10, 'pending', NULL, '2026-09-03'),
  (4, 12, 'paid', 4000, '2026-09-04'),
  (5, 11, 'refunded', 0, '2026-09-05'),
  (6, 13, 'paid', 1500, '2026-09-06'),
  (7, 10, 'paid', 3000, '2026-09-07'),
  (8, 13, 'pending', NULL, '2026-09-08');

-- September paid-order snapshot: one result row, not one row per order.
SELECT COUNT(*) AS paid_orders,
       COUNT(DISTINCT customer_id) AS paying_customers,
       SUM(amount_cents) AS revenue_cents,
       ROUND(AVG(amount_cents), 2) AS average_order_cents,
       MIN(amount_cents) AS smallest_paid_cents,
       MAX(amount_cents) AS largest_paid_cents
FROM orders
WHERE status = 'paid'
  AND placed_on >= '2026-09-01'
  AND placed_on < '2026-10-01';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Verify all six metrics. Exclude September 1 and 2, then explain each changed result.

Lab review criteria

The query returns one summary row. Pending and refunded orders are excluded. Customer count uses COUNT(DISTINCT customer_id); order count uses COUNT(*). Amounts stay in cents, and the date window has an exclusive October upper bound.

Lesson review

COUNT(*) counts rows, while COUNT(column) counts non-null values. SUM and AVG ignore nulls but include zero; MIN and MAX find extremes. An empty population gives zero for count and null for the other common aggregates. A useful metric has a clear population, unit, and treatment of missing values.

  • I can distinguish orders from known amounts.
  • I can calculate an average using the non-null denominator.
  • I can filter a population before naming a metric.
  • I can explain empty-input aggregate results.
  • I know when DISTINCT changes a metric.
  • I can check a summary against source rows.
KNOWLEDGE CHECK

Check aggregate reasoning

Answer all ten questions, then rerun any example whose population or denominator surprised you.

01What does COUNT(*) return for the eight seeded orders?
02What does COUNT(amount_cents) return for the same orders?
03Why is AVG(amount_cents) over all orders 2,000 cents?
04What changes when WHERE status = 'paid' is added before AVG(amount_cents)?
05Which pair gives the smallest and largest known order amounts over all statuses?
06What does an ungrouped aggregate query return when WHERE matches no rows?
07What does COUNT(DISTINCT customer_id) measure in the seeded table?
08Why is SUM(DISTINCT amount_cents) unsafe as a general revenue fix?
09How can one summary row count paid and pending orders separately?
10What will GROUP BY add in the next lesson?
PREVIOUS LESSONText, Dates, Patterns, and Conditional Results
NEXT LESSONGROUP BY and HAVING
ON THIS PAGEAggregate FunctionsLesson mapCOUNT(*)COUNT(column)SUM and AVGFilter firstMIN and MAXEmpty inputDISTINCTConditional countsCommon mistakesIndependent labLesson reviewKnowledge check
Course contents