COUNT, SUM, AVG, MIN, MAX

From lesson 3-2, SELECT DISTINCT genre FROM movies; returns each unique genre exactly once, collapsing the duplicate result rows.

Knowing which values exist is often only half the question. The other half is how many of each there are, and counting is where this lesson starts.

From many rows to one number

Every query so far returned rows, but most business questions want a number: how many users signed up this week, what the store earned today, what the average order size is. Nobody answers those by reading a million rows by hand, and dashboards, reports, and analytics are built almost entirely from what this lesson covers.

Aggregate functions boil many rows down to a single value:

This unit uses a cafe's orders table with the columns customer, item, and price.

Averages often come out as long decimals like 5.83333333333333, so wrapping them in ROUND(value, 2) keeps the output to two decimal places and makes reports readable.

Four questions, four single-row answers

Each SELECT here prints one line: the total number of orders, total revenue, the cheapest and priciest item together, and the rounded average price.

CREATE TABLE orders (
  customer TEXT,
  item TEXT,
  price INTEGER
);

INSERT INTO orders VALUES ('Ana', 'coffee', 5);
INSERT INTO orders VALUES ('Ben', 'sandwich', 8);
INSERT INTO orders VALUES ('Ana', 'bagel', 4);
INSERT INTO orders VALUES ('Cara', 'coffee', 5);
INSERT INTO orders VALUES ('Ben', 'coffee', 5);
INSERT INTO orders VALUES ('Ana', 'sandwich', 8);

SELECT COUNT(*) FROM orders;
SELECT SUM(price) FROM orders;
SELECT MIN(price), MAX(price) FROM orders;
SELECT ROUND(AVG(price), 2) FROM orders;

Output

6
35
4|8
5.83

Six rows go in and a single row comes out of each query, which is the defining behavior of an aggregate. That is why no row-level column such as customer appears in these SELECT lists: with six customers collapsed into one answer, there would be no single correct name to report.

The third query shows that two aggregates can share one SELECT, and both are computed over the same six rows. The last one is 35 ÷ 6 = 5.8333..., cut to 5.83 by ROUND.

Aggregates respect WHERE

An aggregate runs after the row filter, so you can count or sum any slice of the table:

SELECT COUNT(*) FROM orders WHERE item = 'coffee';

First WHERE keeps only the coffee rows, then COUNT counts what is left. This one-two punch (filter, then aggregate) answers most everyday questions: revenue this month, users who never logged in, largest order over $10.

Ana's total spending

Aggregates run over whatever rows survive the WHERE clause, so filtering first and totaling second answers a question about one customer.

CREATE TABLE orders (
  customer TEXT,
  item TEXT,
  price INTEGER
);

INSERT INTO orders VALUES ('Ana', 'coffee', 5);
INSERT INTO orders VALUES ('Ben', 'sandwich', 8);
INSERT INTO orders VALUES ('Ana', 'bagel', 4);
INSERT INTO orders VALUES ('Cara', 'coffee', 5);
INSERT INTO orders VALUES ('Ben', 'coffee', 5);
INSERT INTO orders VALUES ('Ana', 'sandwich', 8);

-- Ana's total spending:
SELECT SUM(price) FROM orders WHERE customer = 'Ana';

Output

17

"How much money" means adding prices up, so this is SUM(price) rather than COUNT. Mixing those two up is the most common aggregate mistake there is: COUNT would have answered 3, the number of Ana's orders, which is a true fact about a different question.

Ana ordered coffee at 5, a bagel at 4, and a sandwich at 8, and 5 + 4 + 8 = 17. The WHERE clause runs before the aggregate, so the other three customers' rows are gone before SUM ever sees them.

COUNT(*) compared with COUNT(column)

COUNT(*) counts rows. COUNT(price) counts rows whose price is not NULL, because aggregate functions skip NULLs entirely, and SUM and AVG skip them too.

The two numbers agree on complete data and disagree exactly where your data has gaps, which is often the useful part. COUNT(*) answers "how many orders are there", while COUNT(price) answers "how many orders have a price on record".

This also explains a subtlety in AVG. Since NULLs are skipped rather than treated as zero, an average is computed over only the rows that have a value, so a column with many gaps can report a healthy average based on very little data.

The next example adds a free tap water with a NULL price and runs both counts side by side.

The NULL row that only one count sees

Seven rows now exist in the table, but only six of them carry a price, so the two counts disagree by exactly one.

CREATE TABLE orders (
  customer TEXT,
  item TEXT,
  price INTEGER
);

INSERT INTO orders VALUES ('Ana', 'coffee', 5);
INSERT INTO orders VALUES ('Ben', 'sandwich', 8);
INSERT INTO orders VALUES ('Ana', 'bagel', 4);
INSERT INTO orders VALUES ('Cara', 'coffee', 5);
INSERT INTO orders VALUES ('Ben', 'coffee', 5);
INSERT INTO orders VALUES ('Ana', 'sandwich', 8);

INSERT INTO orders VALUES ('Dan', 'water', NULL);

SELECT COUNT(*) FROM orders;
SELECT COUNT(price) FROM orders;

Output

7
6

Dan's water is a real order, so COUNT(*) includes it. Its price was never recorded, so COUNT(price) leaves it out.

The difference between two counts like these is a quick and genuinely useful data quality check. If COUNT(*) and COUNT(price) differ on a table where every order is supposed to have a price, you have just found rows worth investigating.