COUNT, SUM, AVG: rows become numbers

Chapter 3 — "The Store's Pulse"

"Analyst" clearance settles onto your pass as a warm amber stripe. K.'s encrypted block never did open, but you know its heading by heart: "Raw rows are noise. Meaning appears once you can add them up." QUERY dims the spare holopanels and leaves one — empty, with a single dot pulsing at its center.

QUERY: You've been reading the archive row by row. Enough. Today we listen to its heart.

Up to this point, you have mostly been retrieving individual rows.

For example:

SELECT id, user_id, status, total_amount
FROM orders
WHERE status = 'paid';

A query like that lists the paid orders one row at a time:

This is useful when you want to examine the individual rows.

But a business question often sounds quite different:

How many paid orders were there?
What is the total revenue?
What is the average order value?
What were the smallest and the largest orders?
How many distinct customers placed an order?

Questions like these do not need a long list of rows. They need a summary instead.

That is where SQL's aggregate functions come in.

An aggregate function takes a set of rows and returns a single summary value.

For example:

SELECT COUNT(*) AS orders_count
FROM orders;

This query does not show each order separately. It returns a single number: the number of rows in the orders table.

Aggregate functions: many rows → one value

Aggregates turn many rows into useful summary values.

Not this:

order 101
order 102
order 103
order 104

But this:

4 orders in total

Not this:

1200
3500
800
2100

But this:

total amount 7600
average order 1900
smallest order 800
largest order 3500

Here are the five main aggregate functions:

For example:

SELECT
    COUNT(*) AS orders_count,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_amount,
    MIN(total_amount) AS min_order_amount,
    MAX(total_amount) AS max_order_amount
FROM orders;

If the query has no GROUP BY, each aggregate computes one summary value over the entire input set.

So the result is a single row:

COUNT: how many rows

COUNT answers the question "How many?"

The most common form is:

COUNT(*)

It counts rows.

For example:

SELECT COUNT(*) AS orders_count
FROM orders;

Result:

The star in COUNT(*) does not mean "select every column". Here, it means:

Count the rows themselves.

COUNT(*) does not inspect the values in any column. All it cares about is whether a row exists.

Use COUNT(*) for questions like these:

How many orders are there in total?
How many products are there in total?
How many users are there in total?
How many rows are left after the filter?

For example, here is how to count the paid orders:

SELECT COUNT(*) AS paid_orders_count
FROM orders
WHERE status = 'paid';

First, WHERE keeps only the paid orders. Then COUNT(*) counts the remaining rows.

SUM, AVG, MIN and MAX

SUM adds values up.

SELECT SUM(total_amount) AS revenue
FROM orders
WHERE status = 'paid';

That gives you the total revenue from paid orders.

AVG computes the average value.

SELECT AVG(total_amount) AS avg_order_amount
FROM orders
WHERE status = 'paid';

That gives you the average order value for paid orders.

MIN finds the smallest value.

SELECT MIN(total_amount) AS min_order_amount
FROM orders
WHERE status = 'paid';

MAX finds the largest value.

SELECT MAX(total_amount) AS max_order_amount
FROM orders
WHERE status = 'paid';

You can use all of these functions together:

SELECT
    COUNT(*) AS paid_orders_count,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_amount,
    MIN(total_amount) AS min_order_amount,
    MAX(total_amount) AS max_order_amount
FROM orders
WHERE status = 'paid';

This gives you a complete summary of the paid orders in a single result row.

WHERE filters the rows first, aggregates summarize them second

It helps to understand the logical order here.

In this query:

SELECT
    COUNT(*) AS paid_orders_count,
    SUM(total_amount) AS revenue
FROM orders
WHERE status = 'paid';

the filter is logically applied first:

WHERE status = 'paid'

It keeps only the paid orders.

Only then do the aggregates compute their results over the remaining rows:

COUNT(*)
SUM(total_amount)

So the query does not aggregate all orders and then filter for paid ones. It first filters out the rows it does not need, then computes the aggregates.

This lets you choose exactly which rows to summarize.

Revenue from paid orders only:

WHERE status = 'paid'

The number of cancelled orders:

WHERE status = 'cancelled'

The average order value during a particular period:

WHERE created_at >= '2024-01-01'
  AND created_at <  '2024-02-01'

First, the filter determines which rows remain. Then each aggregate computes a summary value.

QUERY: Before you take the data's pulse, decide which rows you want to summarize. All orders, paid orders, and cancelled orders tell different stories.

COUNT(*), COUNT(col), COUNT(DISTINCT col)

COUNT comes in several forms, and they answer different questions.

Let's work through a small example.

Suppose you have a visits table:

The query:

SELECT
    COUNT(*) AS rows_count,
    COUNT(promo_code) AS with_promo_code,
    COUNT(DISTINCT promo_code) AS different_promo_codes
FROM visits;

returns:

Why?

COUNT(*) counts every row.

The table has 5 rows, so the result is 5.

COUNT(promo_code) counts the rows where promo_code has a non-NULL value.

It does not count NULL values. Three rows have a non-NULL promo code:

SALE
SALE
VIP

So the result is 3.

COUNT(DISTINCT promo_code) counts the distinct non-NULL values.

Among the non-NULL values:

SALE
SALE
VIP

only two are distinct:

SALE
VIP

So the result is 2.

Putting it all together:

COUNT(*)                 → how many rows
COUNT(promo_code)        → how many rows have a promo code
COUNT(DISTINCT promo_code) → how many distinct promo codes

These ask three different questions of the data, so their answers can differ.

Aggregates and NULL

The general rule is:

Aggregate functions usually ignore NULL.

For example, suppose you have these values:

SUM(total_amount) adds up only the non-NULL values:

1000 + 2000 = 3000

AVG(total_amount) also uses only the non-NULL values:

(1000 + 2000) / 2 = 1500

It does not calculate this:

(1000 + 2000 + NULL) / 3

MIN and MAX likewise consider only non-NULL values when finding the smallest and largest.

The one important exception is COUNT(*).

COUNT(*) counts rows rather than the value of a particular column, so NULL values in those rows do not affect the count.

Compare:

SELECT
    COUNT(*) AS rows_count,
    COUNT(total_amount) AS filled_amounts,
    SUM(total_amount) AS total_sum,
    AVG(total_amount) AS avg_amount
FROM orders;

COUNT(*) answers the question:

how many rows

COUNT(total_amount) answers the question:

how many rows have total_amount filled in

These are not the same thing.

When WHERE leaves no rows at all

Sometimes a filter finds no rows whatsoever.

For example:

SELECT
    COUNT(*) AS orders_count,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_amount,
    MIN(total_amount) AS min_order_amount,
    MAX(total_amount) AS max_order_amount
FROM orders
WHERE status = 'status_that_does_not_exist';

If there are no such orders, the result still contains one row because an aggregate query without GROUP BY returns one summary for its input set.

Its values will be:

COUNT(*) returns 0, because there are no rows.

And SUM, AVG, MIN and MAX return NULL, because there are no values to sum, average, or compare.

If your report needs zero revenue instead of NULL, you can use COALESCE:

SELECT
    COUNT(*) AS orders_count,
    COALESCE(SUM(total_amount), 0) AS revenue
FROM orders
WHERE status = 'status_that_does_not_exist';

This does not change the data in the table. It simply shows 0 in the query result instead of returning NULL for the total.

COUNT(*) and COUNT(1)

You may sometimes see this in queries written by others:

COUNT(1)

For example:

SELECT COUNT(1)
FROM orders;

For a regular row count in PostgreSQL, this is equivalent to:

SELECT COUNT(*)
FROM orders;

Why?

Because 1 is an expression that is never NULL. It has the same non-NULL value for every row, so COUNT(1) counts every row.

For readability, though, it is better to write:

COUNT(*)

That way it is obvious at a glance that you are counting rows.

Do not rely on the myth that COUNT(1) is faster than COUNT(*) in PostgreSQL. For now, remember something simpler:

If you want to count rows, write COUNT(*).

Why an ordinary column next to an aggregate is an error

Look at this query:

SELECT status, COUNT(*)
FROM orders;

At first glance, it may look as though this should show each status with its order count.

But without GROUP BY, this query is invalid.

Why?

COUNT(*) without GROUP BY collapses all the input rows into a single result row.

For example:

But status is an ordinary column. Different orders in the table have different statuses:

If the whole set of rows has collapsed into one result row, which status is SQL supposed to show next to the overall count?

paid?
pending?
cancelled?

SQL does not guess. It asks you to make the logic explicit.

If you want the overall order count, drop status:

SELECT COUNT(*) AS orders_count
FROM orders;

If you want the number of orders per status, add GROUP BY:

SELECT status, COUNT(*) AS orders_count
FROM orders
GROUP BY status;

Then the result is not one overall row but a separate row for each status:

We will go through GROUP BY in detail later on. For now, what matters is the reason for the error:

An aggregate produces one value for the entire set, while an ordinary column may have several possible values. SQL will not choose one for you.

QUERY: Raw rows show individual events. Aggregates let you take the data's pulse: how many there are, what they add up to, and their average, minimum, and maximum values.