Aggregate functions (COUNT, SUM, AVG)

The idea

Most business questions aren't "show me the rows", they're "give me a number": how many orders did we get, what's our total revenue, what's the average salary? Aggregate functions answer them. They take a whole column of values and squash it into a single value.

The five you'll use daily

SELECT COUNT(*) AS products,
       MIN(price) AS cheapest,
       MAX(price) AS priciest,
       AVG(price) AS avg_price
FROM products;

 products | cheapest | priciest | avg_price
----------+----------+----------+---------------------
       15 |    38000 |   320000 | 131066.666666666667
(1 row)

Fifteen rows went in, one row came out. That's what aggregates do.

The average has a lot of decimals. ROUND fixes that, as you saw in the expressions module:

SELECT ROUND(AVG(price)) AS avg_price
FROM products;

 avg_price
-----------
    131067
(1 row)

MIN and MAX work on text (alphabetical) and dates (earliest/latest) too:

SELECT MIN(order_date) AS first_order,
       MAX(order_date) AS last_order
FROM orders;

 first_order | last_order
-------------+------------
 2024-01-03  | 2024-06-29
(1 row)

Aggregating a calculation

The revenue of an order line is quantity * unit_price. Put the calculation inside SUM, and it's computed per row, then added up:

SELECT SUM(quantity) AS units_sold,
       SUM(quantity * unit_price) AS revenue
FROM order_items;

 units_sold | revenue
------------+----------
        117 | 17502000
(1 row)

Aggregates with WHERE

Usually you don't want to count everything. "How many orders did we get?" is less useful than "how many orders actually went through?", because cancelled orders didn't bring in any money. You can combine an aggregate with WHERE. The WHERE runs first, throwing away the rows that don't match, and the aggregate only sees what's left:

SELECT COUNT(*) AS completed_orders
FROM orders
WHERE status = 'completed';

 completed_orders
------------------
               26
(1 row)

The three COUNTs

"How many customers do we have?", "how many have given us an email?" and "how many cities are they in?" all sound like counting questions, and they are, but they each need a slightly different COUNT. Mixing them up is a classic mistake, so here are all three side by side:

SELECT COUNT(*) AS customers,
       COUNT(email) AS with_email,
       COUNT(DISTINCT city) AS cities
FROM customers;

 customers | with_email | cities
-----------+------------+--------
        12 |         10 |      8
(1 row)

• COUNT(*) counts rows, no matter what's in them: 12 customers.
• COUNT(column) counts rows where that column is not NULL: two customers have no email, so 10.
• COUNT(DISTINCT column) counts different non-NULL values: 8 cities (Jakarta counts once, and the unknown city doesn't count).

What you can't do (yet)

Try to show a product name next to the highest price:

SELECT name, MAX(price)
FROM products;

ERROR: column "products.name" must appear in the GROUP BY clause or be used in an aggregate function

It sounds reasonable, but think about it from the database's side: MAX(price) squashes 15 rows into 1, while name still has 15 values. Which name should go on that single row? SQL refuses to guess. The error mentions GROUP BY, which is the next module: it lets you aggregate per group, such as the highest price per category. (To find which product is the most expensive, you already know a simpler way: ORDER BY price DESC LIMIT 1.)

Empty input

When no rows match, COUNT returns 0, but SUM, AVG, MIN and MAX return NULL, because there's nothing to add up:

SELECT COUNT(*) AS n, SUM(salary) AS total
FROM employees
WHERE department = 'Legal';

 n | total
---+-------
 0 |  NULL
(1 row)

Remember this when a dashboard shows an empty cell instead of 0: there were simply no rows.

Why this matters for clean code

Doing the maths in the database means one query, one number, the same for everyone. Downloading every row to Excel to add them up is slow and invites copy-paste mistakes. Name every aggregate (AS revenue, AS completed_orders), and pick the right COUNT deliberately, so a reader knows exactly what's being counted.

Recap

• Aggregates squash many rows into one value: COUNT, SUM, AVG, MIN, MAX.
• Put calculations inside them: SUM(quantity * unit_price).
• WHERE filters rows before they're aggregated.
• COUNT(*) counts rows; COUNT(col) skips NULLs; COUNT(DISTINCT col) counts unique values.
• Aggregates ignore NULLs; on no rows, SUM/AVG give NULL and COUNT gives 0.
• You can't mix a plain column with an aggregate without GROUP BY.