Counting and summing

One row out of many

An aggregate reads every row the where clause let through and returns a single value. Ask for one and you get exactly one row back, even from an empty table — and that matters, because count returns 0 while sum returns NULL.

the five that matter

select count(*)      as doors,        -- 10
       min(price)    as cheapest,     -- 520
       max(price)    as dearest,      -- 4200
       round(avg(price), 2) as average,   -- 1734.05
       sum(price)    as if_we_bought_one_of_each
from   doors;

count(*) and count(column) are different questions

twelve customers, two with no city

select count(*)         as rows,          -- 12: how many customers
       count(city)      as with_city,     -- 10: how many have one
       count(distinct country) as countries  -- 5
from   customers;

count(column) skips NULLs — every aggregate does

avg(price) over ten prices where three are NULL is the average of seven, not of ten with zeros. If you want the zeros, say avg(coalesce(price, 0)). This is right far more often than people expect, and wrong in exactly the cases nobody checks.

sum of nothing is NULL

A dashboard that prints "null hours" instead of "0" is this rule arriving at the front end. count is the exception: no rows means 0.

guard the empty case

select sum(hours) from jobs where fitter_id = 99;   -- null, not 0

select coalesce(sum(hours), 0) as hours
from   jobs where fitter_id = 99;                    -- 0

Aggregating an expression

unit_price is stored per line and not read from doors, on purpose: a price that moves must not rewrite history. Every invoice you have ever received does the same thing.

the line total does not exist as a column — compute it

select sum(qty * unit_price) as revenue,     -- 139184.5
       sum(qty)              as doors_sold,  -- 113
       round(avg(qty * unit_price), 2) as average_line
from   order_items;

distinct inside an aggregate

twenty-four orders, eleven customers

select count(*)                    as orders,      -- 24
       count(distinct customer_id) as customers    -- 11
from   orders;

An aggregate cannot sit beside a plain column

max(price) collapses ten rows into one; name has ten different answers. PostgreSQL refuses, MySQL and SQLite pick one arbitrarily — which is worse, because it looks right. The next lesson, group by, is the rule that makes such a query legal.

the error everybody meets once

-- "which door is the dearest?" — this is not the way
select name, max(price) from doors;     -- Postgres: error. SQLite: a random name

-- ask for the row instead
select name, price from doors order by price desc limit 1;

SQLite has a documented exception

With a bare min() or max() and no group by, SQLite promises the other columns come from that row. It is genuinely useful and completely unportable — do not learn it as the way.