Basic

Counting instead of listing

I needed to know how many rows matched, so I printed them all and counted with my eyes. COUNT does the counting inside the database and sends back one number.

What you will see

max 6 sqlite>

About 2 min of reading

Try It

Ask the database to count and total instead of listing, and watch it return single numbers.

Count every row in the table.

SELECT COUNT(*) FROM users;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

count 6 sqlite>

Count only the rows that pass a filter. Three rows have an id above 3.

SELECT COUNT(*) FROM users WHERE id > 3;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

count 3 sqlite>

Add a column up. The ids 1 through 6 sum to 21.

SELECT SUM(id) FROM users;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

sum 21 sqlite>

Find the largest id in one step, no sorting needed.

SELECT MAX(id) FROM users;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

max 6 sqlite>

Data Flow

FROM reads rows, WHERE filters them, and an aggregate function collapses the survivors into one number.

1 FROM reads the rows

↓

2 WHERE filters them

↓

3 COUNT or SUM sees survivors

↓

4 One number returns

Why this works

Aggregate functions

Some functions take many rows and return one value. These are the aggregate functions:

COUNT(*) rows, COUNT(col) non-blank values in a column
SUM(col) total, AVG(col) mean
MIN(col) smallest, MAX(col) largest

They collapse a whole set of rows into a single answer, which is why they are the cheap way to ask how big or how much.

WHERE runs before the count

The order inside a query is fixed and it matters here. FROM picks the table, WHERE throws out rows, and only then do the aggregates see what is left. So COUNT(*) WHERE id > 3 counts the survivors, not the table. You are not counting and then filtering, you are filtering and then counting.

Examples

How many rows

SELECT COUNT(*) FROM users;

COUNT is a function that counts rows. The star means every row. The answer is a single number, no matter how big the table is.

Count with a filter

SELECT COUNT(*) FROM users WHERE id > 3;

WHERE still runs first, so COUNT only sees the rows that passed. This counts the matching rows, not the whole table.

Add them up

SELECT SUM(id) FROM users;

SUM adds a column. AVG averages it, MIN finds the smallest, MAX the largest. Each returns one number for the rows that matched.

Words this lesson uses

Each one is defined here on this site. Open the entry for the full definition and the other words it points at.

← Changing one row

Previous Lesson

Changing one row

Next Lesson

Putting rows in order

Putting rows in order →