Aggregation
Summarizing rows with aggregate functions
A table can contain thousands or millions of rows, but sometimes we don't want the individual rows.
Instead, we want a summary.
For example:
123456789How many orders are there?What is the total value of the orders?What is the average order amount?What is the smallest order?What is the largest order?PostgreSQL provides aggregate functions for these kinds of calculations.
An aggregate function performs a calculation across multiple input rows and returns a single summary value.
Some of the most commonly used aggregate functions are:
12345COUNT()SUM()AVG()MIN()MAX()Start with a few rows
Run:
123456SELECT id, total_amountFROM ordersWHERE id <= 5ORDER BY id;You should see:
1234567 id | total_amount----+-------------- 1 | 10.01 2 | 10.02 3 | 10.03 4 | 10.04 5 | 10.05We can use these rows to see what aggregate functions do.
COUNT
COUNT() counts rows.
Run:
123SELECT COUNT(*) AS order_countFROM ordersWHERE id <= 5;You should see:
123 order_count------------- 5The query selected five rows, so COUNT(*) returned 5.
Conceptually:
123456789101110.0110.0210.0310.0410.05 │ ▼COUNT(*) │ ▼ 5SUM
SUM() adds values together.
Run:
123SELECT SUM(total_amount) AS totalFROM ordersWHERE id <= 5;You should see:
123 total------- 50.15PostgreSQL added:
110.01 + 10.02 + 10.03 + 10.04 + 10.05 = 50.15AVG
AVG() calculates the average.
Run:
123SELECT ROUND(AVG(total_amount), 2) AS average_amountFROM ordersWHERE id <= 5;You should see:
123 average_amount---------------- 10.03PostgreSQL first considers all five values and calculates their arithmetic mean.
MIN and MAX
MIN() returns the smallest value:
123SELECT MIN(total_amount) AS smallest_orderFROM ordersWHERE id <= 5;You should see:
123 smallest_order---------------- 10.01MAX() returns the largest:
123SELECT MAX(total_amount) AS largest_orderFROM ordersWHERE id <= 5;You should see:
123 largest_order--------------- 10.05Multiple aggregates in one query
We don't need a separate query for every calculation.
Run:
12345678SELECT COUNT(*) AS order_count, SUM(total_amount) AS total_amount, ROUND(AVG(total_amount), 2) AS average_amount, MIN(total_amount) AS minimum_amount, MAX(total_amount) AS maximum_amountFROM ordersWHERE id <= 5;You should see:
123 order_count | total_amount | average_amount | minimum_amount | maximum_amount-------------+--------------+----------------+----------------+---------------- 5 | 50.15 | 10.03 | 10.01 | 10.0512345678910111213Rows10.0110.0210.0310.0410.05 │ ├──── COUNT ────▶ 5 ├──── SUM ──────▶ 50.15 ├──── AVG ──────▶ 10.03 ├──── MIN ──────▶ 10.01 └──── MAX ──────▶ 10.05The input contains multiple rows.
Each aggregate produces one summary value.
WHERE filters rows before aggregation
Aggregate functions operate on the rows that reach them.
For example:
123SELECT COUNT(*) AS processing_ordersFROM ordersWHERE status = 'processing';The WHERE clause first keeps only processing orders.
Then COUNT(*) counts those rows.
Conceptually:
12345678910orders │ ▼WHERE status = 'processing' │ ▼processing rows │ ▼COUNT(*)You should get:
123 processing_orders------------------- 8000This distinction becomes especially important once we start grouping rows.
COUNT(*) and COUNT(column)
There is an important difference between:
1COUNT(*)and:
1COUNT(column)COUNT(*) counts rows.
COUNT(column) counts rows where that expression is not NULL.
Our orders.shipped_at column can contain NULL.
Run:
1234SELECT COUNT(*) AS total_orders, COUNT(shipped_at) AS orders_with_shipped_atFROM orders;You should see:
123 total_orders | orders_with_shipped_at--------------+------------------------ 100000 | 85000There are 100000 rows in total.
But 15000 orders have shipped_at = NULL, so:
1COUNT(shipped_at)counts only 85000.
Those 15000 rows are exactly the processing, cancelled, and pending orders, which have not shipped yet.
A useful mental model is:
12345COUNT(*)→ count rowsCOUNT(column)→ count non-NULL values in that columnAggregate functions and NULL
Most commonly used aggregate functions ignore NULL inputs.
For example:
12345SUM()AVG()MIN()MAX()COUNT(column)normally operate only on non-NULL input values.
COUNT(*) is different because it counts rows regardless of whether individual column values are NULL.
For example:
123row 1 shipped_at = timestamprow 2 shipped_at = NULLrow 3 shipped_at = timestampthen:
12COUNT(*) → 3COUNT(shipped_at) → 2What happens when no rows match?
There is another important difference between COUNT() and most other aggregates.
Run:
123SELECT COUNT(*) AS order_countFROM ordersWHERE id = -1;No rows match, so you should see:
123 order_count------------- 0Now run:
123SELECT SUM(total_amount) AS total_amountFROM ordersWHERE id = -1;You should see:
123 total_amount-------------- NULLWhen there are no input rows:
1COUNT(*) → 0while aggregates such as:
1234SUM()AVG()MIN()MAX()return NULL.
This is something application code often needs to handle.
For example, if an API needs a numeric total even when there are no orders, you might use:
123SELECT COALESCE(SUM(total_amount), 0) AS total_amountFROM ordersWHERE id = -1;which returns:
123 total_amount-------------- 0Aggregating the entire result
So far, our aggregate queries returned one row.
For example:
12SELECT COUNT(*)FROM orders;asks PostgreSQL to summarize all matching orders together.
But often we want several summaries.
For example:
12345How many delivered orders are there?How many shipped orders are there?How many processing orders are there?We could write several separate queries.
But PostgreSQL can divide the rows into groups and calculate an aggregate for each group.
That is what GROUP BY does.
The important point is:
Aggregate functions reduce multiple input rows into summary values.