So far every query returned one row per matching input row. Aggregate functions collapse a set of rows down to a single value — and combined with GROUP BY, they let you compute "one number per group".
The seed has the familiar users table plus a new orders table (15 rows) with a user_id, product, and amount. We'll mostly aggregate over orders.
Aggregate functions
The five you'll use most:
count(*)— number of rowscount(col)— number of rows wherecolis not NULL (subtle but important)sum(col),avg(col)— numeric totals and meansmin(col),max(col)— extremes (work on numbers, text, dates)
SELECT
count(*) AS total_orders,
sum(amount) AS revenue,
avg(amount)::numeric(10, 2) AS avg_order,
min(amount) AS smallest,
max(amount) AS biggest
FROM orders;
The whole table collapsed into one row. avg returns extra precision by default — casting to numeric(10, 2) keeps it tidy.
count(*) vs count(column)
This trips people up. count(*) counts rows. count(column) counts rows where that column is not null — handy when a column is sparsely populated.
SELECT
count(*) AS users_total,
count(country) AS users_with_country
FROM users;
We updated the seed for this lesson, so this one happens to have all twelve filled in. Try it on the lesson 03 seed and you'll see ten of sixteen.