Aggregate Functions

Aggregate functions turn many rows into one answer. COUNT, SUM, AVG, MIN, and MAX are the tools behind totals, averages, inventory counts, "cheapest item" questions, and the first real reports you will write in SQL.

This lesson is the one-pile version: take the rows, apply the aggregate, combine them into one summary row. The next lesson adds the split step, where each category gets its own pile and its own answer.

One result row from many input rows

The cafe menu has ten items. Ask five aggregate questions at once:

SELECT COUNT(*) AS items,
       SUM(price) AS total_price,
       AVG(price) AS average_price,
       MIN(price) AS cheapest,
       MAX(price) AS priciest
FROM menu_items;

 items | total_price |   average_price    | cheapest | priciest
-------+-------------+--------------------+----------+----------
    10 |       34.30 | 3.4300000000000000 |     2.20 |     4.50
(1 row)

Ten rows went in. One row came out. That is the defining move of an aggregate without GROUP BY: it collapses the selected rows into a single answer row.

Read each function by the question it answers:

• COUNT(*): how many rows?
• SUM(price): what is the total?
• AVG(price): what is the average?
• MIN(price): what is the smallest value?
• MAX(price): what is the largest value?

PostgreSQL prints the exact numeric average with a lot of scale. The important part is not the formatting yet; it is the collapse: one answer row, no item names left.

The COUNT trap: rows are not values

Now count the cafe staff. A beginner's first attempt often looks like this:

SELECT COUNT(phone) AS staff_count
FROM staff;

 staff_count
-------------
           4
(1 row)

The staff table has six people, so 4 is a quietly wrong answer. The query did not count staff rows. It counted known phone values.

Put the counts side by side and the meaning becomes visible:

SELECT COUNT(*) AS staff_rows,
       COUNT(phone) AS phones_on_file,
       COUNT(bonus) AS decided_bonuses
FROM staff;

 staff_rows | phones_on_file | decided_bonuses
------------+----------------+-----------------
          6 |              4 |               4
(1 row)

COUNT(*) looked at the rows. COUNT(phone) skipped Ada and Yuki because their phone is NULL. COUNT(bonus) skipped Rosa and Tomas because their bonus is NULL.

Most aggregates ignore NULL too

The NULL rule is not just for COUNT(column). SUM, AVG, MIN, and MAX also ignore NULL inputs. Usually that is exactly right: an unknown bonus should not poison the sum of the bonuses that have actually been decided.

But it can still be subtly wrong if the business sentence changes. Compare "average decided bonus" with "average bonus per staff member, treating missing as zero for this report":

SELECT SUM(bonus) AS total_decided_bonus,
       AVG(bonus) AS average_decided_bonus,
       SUM(COALESCE(bonus, 0)) AS total_with_missing_as_zero,
       AVG(COALESCE(bonus, 0)) AS average_with_missing_as_zero
FROM staff;

 total_decided_bonus | average_decided_bonus | total_with_missing_as_zero | average_with_missing_as_zero
---------------------+-----------------------+----------------------------+------------------------------
              770.00 |  192.5000000000000000 |                     770.00 |         128.3333333333333333
(1 row)

Same total, different average. AVG(bonus) divides by four decided bonus values. AVG(COALESCE(bonus, 0)) divides by six staff rows because every row now contributes a value. Neither query is automatically more honest. The honest query is the one whose denominator matches the sentence on the report.

DISTINCT counts unique known values

COUNT(DISTINCT column) answers "how many different known values appear here?" It is still an aggregate, so without grouping it still returns one row:

SELECT COUNT(DISTINCT category) AS categories,
       COUNT(DISTINCT in_stock) AS stock_states,
       COUNT(1) AS count_one,
       COUNT(*) AS count_star
FROM menu_items;

 categories | stock_states | count_one | count_star
------------+--------------+-----------+------------
          4 |            2 |        10 |         10
(1 row)

There are four categories and two stock states: true and false. COUNT(DISTINCT ...) also skips NULL, just like COUNT(column).

The COUNT(1) column is there to retire an old myth. In PostgreSQL, COUNT(1) is not a faster version of COUNT(*); it is just a less clear way to ask for the row count. Write COUNT(*) when you mean rows.

Conditional aggregation: count several answers at once

A normal WHERE clause filters the whole query down to one set of rows. Conditional aggregation lets each aggregate bring its own filter, so one scan can answer several related questions:

SELECT COUNT(*) FILTER (WHERE in_stock = true) AS stocked_items,
       COUNT(*) FILTER (WHERE in_stock = false) AS sold_out_items,
       SUM(CASE WHEN price >= 4 THEN 1 ELSE 0 END) AS premium_items
FROM menu_items;

 stocked_items | sold_out_items | premium_items
---------------+----------------+---------------
             8 |              2 |             3
(1 row)

FILTER (WHERE ...) belongs to the aggregate beside it. The first count sees only stocked rows; the second sees only sold-out rows. The query still returns one row because there is still no grouping.

The CASE version is the older portable trick you previewed in 3.3: turn each row into a 1 or 0, then add the markers. It is especially useful when you need the same pattern in a database that does not support PostgreSQL's FILTER syntax.

Try the pattern in the playground. Change the price cutoff, add another filtered count, and watch the single answer row update.

That last commented line is the boundary of this lesson. A plain aggregate collapses the table, so there is no individual name left to print beside the count. Next, GROUP BY adds the split step: one pile per category, one answer row per pile.