Aggregates and GROUP BY

Many rows into one value

An aggregate function reads many rows and returns one value:

SELECT min(price), max(price), round(avg(price), 2) AS avg_price, sum(price) FROM products;

  min   |   max    | avg_price |   sum
--------+----------+-----------+----------
 499.00 | 18999.00 |   6481.09 | 71292.00

count, and what it skips

count comes in three forms, and they answer different questions:

SELECT count(*) AS customers, count(city) AS with_city, count(DISTINCT city) AS distinct_cities
FROM customers;

 customers | with_city | distinct_cities
-----------+-----------+-----------------
         8 |         6 |               5

• count(*) counts rows: 8 customers.
• count(city) counts rows where city is not NULL: Elena and Hugo are skipped, so 6.
• count(DISTINCT city) counts different non-NULL values: Pune appears twice, so 5.

Every other aggregate (sum, avg, min, max) also ignores NULLs. avg(city_population) would be the average of the known values, not of every row.

GROUP BY: one result per group

GROUP BY splits the rows into groups that share a value, and computes the aggregates once per group:

SELECT category, count(*) AS products, min(price), max(price)
FROM products
GROUP BY category
ORDER BY products DESC, category;

  category   | products |   min    |   max
-------------+----------+----------+----------
 Accessories |        5 |   499.00 |  4500.00
 Furniture   |        2 |  2199.00 | 14500.00
 Audio       |        1 | 12999.00 | 12999.00
 …

Grouping fits into the evaluation order between WHERE and SELECT:

Aggregates work across joins too. The three largest orders by value:

SELECT o.id AS order_id, sum(oi.quantity * oi.unit_price) AS order_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id
ORDER BY order_total DESC
LIMIT 3;

 order_id | order_total
----------+-------------
      102 |    37998.00
      109 |    23499.00
      105 |    18898.00

The grouping rule

SELECT category, name, count(*) FROM products GROUP BY category;

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

The sum of nothing

SELECT sum(price) FROM products WHERE price > 100000;   -- NULL, not 0
SELECT count(*)   FROM products WHERE price > 100000;   -- 0

Except for count, aggregates return NULL when there are no rows. A report showing “revenue: NULL” or code that crashes on a missing number usually comes from this. Use coalesce(sum(price), 0) when you want zero.

Check yourself

1. What is the difference between count(*) and count(city)?

   Show answer

   count(*) counts all rows. count(city) counts only the rows where city is not NULL.

   I could explain this without looking

2. Why is SELECT category, name, count(*) FROM products GROUP BY category an error?

   Show answer

   Each output row represents a whole category, which has many names. A selected column must be in GROUP BY or inside an aggregate.

   I could explain this without looking

3. What does sum(amount) return when no rows match, and how do you get 0 instead?

   Show answer

   NULL. Wrap it: coalesce(sum(amount), 0).

   I could explain this without looking