Aggregate Functions

So far, many of your queries have shown one result row for each row in a table. That is useful when you want details.

But many everyday questions are not detail questions. They are summary questions:

• How many orders did we receive?
• What is the total amount sold?
• What is the average rating?
• What was the smallest or largest value?

Aggregate functions answer questions like these by reading many rows and returning one summary value.

From many rows to one answer

Think of an aggregate as a tiny summarizing machine. Many rows go in. One value comes out.

The five core aggregate functions are:

Counting rows with COUNT(*)

COUNT(*) counts rows. It does not care what values are inside those rows. It simply answers: "How many rows are in this result?"

The table has four order rows, so the answer is one row containing the number 4.

Adding values with SUM

SUM(column) adds the non-NULL values in a column. This is how you answer total questions.

SQLite reads the amount values and adds them into one total.

Finding an average with AVG

AVG(column) finds the average of the non-NULL values in a column. You can use ROUND when you want a friendlier number.

The 2 in ROUND(..., 2) means "show two digits after the decimal point."

QuestionSelect one

What does an aggregate function do?

It shows every row exactly as it appears in the table.

It combines many rows into a smaller summary, often one value.

It changes the table's column names permanently.

It always sorts rows alphabetically.

Finding the smallest and largest values

MIN(column) returns the smallest value. MAX(column) returns the largest value.

These functions are useful for "best," "worst," "earliest," "latest," "cheapest," and "most expensive" questions.

Building a one-row report

You can use several aggregates in the same query. Each aggregate looks at the same set of rows and produces its own summary value.

One query gives you a compact report: count, total, average, smallest, and largest.

Aggregates respect WHERE

WHERE filters rows first. Then the aggregate summarizes only the rows that survived the filter.

Read this as: "Keep only Ada's rows, then add their amounts."

NULL values are skipped by most aggregates

NULL means a value is missing or unknown. Most aggregate functions skip NULL values:

• SUM(column) skips NULL
• AVG(column) skips NULL
• MIN(column) skips NULL
• MAX(column) skips NULL
• COUNT(column) skips NULL

The big exception is COUNT(*), which counts rows whether they contain NULL or not.

There are four customers, but only two have phone numbers. COUNT(*) returns 4; COUNT(phone) returns 2.

NULL and averages

Skipping NULL usually gives a better answer than treating unknown values as zero.

The average is based on the three known ratings: 5, 4, and 3. The missing rating is not counted as zero.

Check your understanding

QuestionSelect one

Which function answers "How many rows are in this result?"

COUNT(*)

SUM(*)

AVG(*)

MAX(*)

QuestionSelect one

A table has 5 rows. The email column is NULL in 2 rows. What will COUNT(email) return?

5

3

2

0

QuestionSelect one

Why does AVG(rating) skip NULL ratings?

Because SQLite cannot store missing values.

Because NULL means unknown, and unknown values should not be averaged as if they were real numbers.

Because AVG only works on text.

Because NULL is always the same as zero.

QuestionSelect one

What does this query return: SELECT MIN(price), MAX(price) FROM products;?

One row for every product.

One row containing the smallest price and the largest price.

A sorted list of all prices.

Only products with missing prices.