Totals, and the groups they belong to

Five functions, one idea

An aggregate turns many rows into one value: counting counts, sum(total) adds, avg(total) divides, min and max pick ends. On their own they collapse the whole table to a single row — which is a legitimate query, and rarely the one you wanted.

GROUP BY is what makes them useful: GROUP BY customer_id runs the aggregate once per distinct value, so each customer gets their own sum. The rule to hold on to is that every column in the SELECT is either inside an aggregate or named in the GROUP BY — the database will insist on it, because otherwise it would have to pick one value out of many and pretend.

Having, and filtering the input

A WHERE clause filters rows before grouping; a HAVING clause filters the groups afterwards. "Customers who spent more than a hundred" is a HAVING, because the number being compared does not exist until the sum is computed: HAVING sum(total) > 100. The two clauses are not interchangeable, and a query with both reads in order — first the rows, then the groups.

FILTER (WHERE ...) is the third way, and it filters the input of one aggregate without touching the others: a count filtered to total > 100 counts the large orders while an unfiltered count in the same SELECT counts all of them. It is PostgreSQL syntax over the standard, and it turns three queries into one row.

Aggregating text, and rounding money

Aggregates are not only for numbers: string_agg(title, ', ' ORDER BY title) collects text into one line — the natural way to show the products belonging to a group. array_agg does the same and keeps them as an array, which is what a JSON or application layer usually prefers.

Averages of numeric are exact, and round(avg(total), 2) says how many decimals the answer should carry. Watch one trap: counting rows over a join counts matched pairs, not entities, since a customer with three orders appears three times — the next chapter uses a subquery to fix exactly that.

How a group is built

The planner usually groups in one of two ways: it sorts the rows by the grouping keys and walks the sorted run, or it builds a hash table with one entry per group. Sorting is better when the data already has an index in that order; hashing is better when it does not and the group count is manageable. Either way the result is one row per distinct combination of keys.

NULL is a value for grouping purposes. All rows whose key is NULL land in the same group, because grouping asks whether values are equal for the purpose of collecting them, and it treats the unknowns as one bucket. That is different from a WHERE comparison, and it is a useful place to look when a report has an unexpected blank label.

An aggregate only knows the rows it is given

The number an aggregate returns is exactly the sum, count or average of the rows that reached it. A join that duplicated rows doubles the sum; a WHERE that removed rows lowers the count; a filter placed after grouping cannot change the aggregate that was already computed. Reading a grouped query means asking what one row of its input represents.

FILTER is the precise tool for one aggregate with a narrower input: it sees each input row and decides whether that row joins this calculation. The other aggregates in the same SELECT still see every row, which is how a single pass can report total orders and large orders side by side.

Try these next

• Group the sales by region and product together, and count the groups.
• Add a row with a NULL region and watch which group it joins.
• Compare count(DISTINCT rep) with count(rep) after joining a second table.

One row per group, and one row of answers

-- The whole table, collapsed: three aggregates on one row.
SELECT count(*) AS orders,
       sum(total) AS revenue,
       round(avg(total), 2) AS average
FROM orders;

-- One row per customer.
SELECT customer_id,
       count(*) AS orders,
       sum(total) AS spent,
       min(placed_on) AS first_order
FROM orders
GROUP BY customer_id
ORDER BY spent DESC;

-- Groups filtered after the sum exists.
SELECT customer_id, sum(total) AS spent
FROM orders
GROUP BY customer_id
HAVING sum(total) > 100;

-- One aggregate, filtered — the others untouched.
SELECT customer_id,
       count(*) AS orders,
       count(*) FILTER (WHERE total > 100) AS big_orders
FROM orders
GROUP BY customer_id
ORDER BY customer_id;