Aggregation:
Grouping & Summarizing

In Module 00 you filtered, sorted, and reshaped individual rows. In Module 01 you stitched tables together with joins, but the result was still a list of rows, one per matched pair. Every query you have written returns roughly "give me these rows." Aggregation breaks that pattern entirely.

On Maya's second morning her manager drops by with three questions: how many employees are there, what is the total of all orders, and what is the average salary per department? An aggregate query asks a fundamentally different thing than the row listings she already knows: "summarize many rows into one number." She is no longer looking at rows, she is looking at summaries of sets of rows. That single mental flip is what this whole module teaches.

We use the same three tables as every module: departments, employees, and orders. As a reminder, the six employees are Alice (dept 10, 185000), Bob (dept 10, 120000), Carol (dept 20, 135000), Dan (dept 20, 98000), Eve (dept 30, 105000), and Frank (dept NULL, 72000). That deliberate NULL on Frank is going to teach us several lessons before we are done.

Before grouping enters the picture, see what an aggregate does on its own. Maya starts with the easiest of her manager's questions, the whole-company totals. The five functions she will reach for constantly are COUNT, SUM, AVG, MIN, and MAX. Each takes a column (or *) and reduces every row in the table down to one value.

SELECT COUNT(*)      AS headcount,   -- how many rows
       SUM(salary)    AS payroll,     -- total of the column
       AVG(salary)    AS avg_salary,  -- arithmetic mean
       MIN(salary)    AS lowest,      -- smallest value
       MAX(salary)    AS highest      -- largest value
FROM employees;

-- result: exactly ONE row, no matter how many input rows
 headcount | payroll | avg_salary | lowest | highest
-----------+---------+------------+--------+---------
         6 |  715000 |  119166.67 |  72000 |  185000

Notice what happened: six input rows became one output row. That is the defining trait of an aggregate query with no GROUP BY. The entire table is treated as a single group, and you get a single summary row back. You can mix several aggregates in one SELECT; they all fold the same set of rows independently. Maya has answered question one with a single line.

This trips up people who think they know COUNT cold. There are three distinct behaviours, and the difference is entirely about how each one treats NULL and duplicates.

Our employees table is the perfect demonstration because Frank's dept_id is NULL, and several employees share a department. Watch the three counts diverge:

SELECT COUNT(*)                  AS all_rows,
       COUNT(dept_id)            AS with_dept,
       COUNT(DISTINCT dept_id)   AS distinct_depts
FROM employees;

Read the result carefully. all_rows = 6 counts every employee, Frank included. with_dept = 5 drops Frank, because his dept_id is NULL and COUNT(col) only counts non-NULL values. distinct_depts = 3 collapses the five remaining department values (10, 10, 20, 20, 30) down to the three unique ones, and still ignores Frank's NULL. Three different numbers from the same six rows. Maya files that away; it is going to matter the moment someone phrases a question imprecisely.

A practical consequence: if someone asks "how many employees have a department assigned?" the answer is COUNT(dept_id), not COUNT(*). If they ask "how many different departments do we staff?" it is COUNT(DISTINCT dept_id). Picking the wrong one gives a confidently-wrong number.

Question three on Maya's list, average salary per department, is where the whole-table trick runs out. So far every aggregate folded the whole table into one number. GROUP BY changes that: it splits the rows into buckets by some key, then runs the aggregate once inside each bucket. One output row per distinct group value.

The grouping key (dept_id) appears in two places: in GROUP BY to define the buckets, and in SELECT so the output tells you which group each summary belongs to. Picture the fold happening physically, the way Maya sketched it on a sticky note:

Six input rows became four group rows: three real departments plus Frank's NULL bucket (more on that in section 5). Here is the query and its result:

SELECT dept_id,
       COUNT(*)   AS headcount,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
ORDER BY dept_id;

 dept_id | headcount | avg_salary
---------+-----------+------------
      10 |         2 |  152500.00
      20 |         2 |  116500.00
      30 |         1 |  105000.00
  (null) |         1 |   72000.00

Here is the single rule that governs every aggregate query, and the one beginners break constantly:

Maya hits this rule within the hour, when she tries to add employee names to her per-department report. Why does it fail? Think about what a group row is. After grouping by dept_id, the bucket for department 10 holds two people: Alice and Bob. The output is one row for that bucket. If she asks for a bare name, the database faces an impossible choice. Which name goes in that single row, Alice or Bob? There is no sensible answer, so it refuses.

-- ERROR: "name" is neither grouped nor aggregated
SELECT dept_id, name, AVG(salary)
FROM employees
GROUP BY dept_id;
-- ERROR:  column "employees.name" must appear in the GROUP BY
--         clause or be used in an aggregate function

-- FIX A: aggregate it, e.g. list names per group
SELECT dept_id, STRING_AGG(name, ', '), AVG(salary)
FROM employees
GROUP BY dept_id;

-- FIX B: add it to GROUP BY, but this changes the buckets!
SELECT dept_id, name, AVG(salary)
FROM employees
GROUP BY dept_id, name;   -- now one group PER person

Notice that the two fixes mean very different things. Fix A keeps one row per department and folds all the names together with STRING_AGG. Fix B adds name to the grouping key, which makes each (dept_id, name) pair its own bucket, and since every person is unique, you are basically back to one row per employee. Maya wants the per-department summary, so Fix A is what her manager actually asked for. Choose based on the question you are actually answering.

Module 00 warned you that NULL bends the rules. Aggregation adds two more behaviours you must know cold, because they quietly change your numbers.

1. NULL forms its own group

When you GROUP BY dept_id, all the rows with a NULL department do not vanish and do not merge into some other bucket. They collect into a single NULL group of their own. That is why Frank produced his own (null) row in section 3. In grouping, all NULLs are treated as "the same unknown" and bucketed together, even though NULL = NULL is never true in a WHERE. Grouping is the one place SQL treats NULLs as equal.

2. Aggregates skip NULLs

This is the bigger trap, and the one that nearly sends Maya's report out wrong. Every aggregate except COUNT(*) ignores NULL inputs entirely. The critical consequence is for AVG: it computes SUM(col) / COUNT(col), that is, the total divided by the count of non-NULL values, not divided by the total number of rows.

-- Imagine Frank's salary were NULL instead of 72000.
-- SUM = 643000 over the 5 known salaries.

SELECT AVG(salary)              AS avg_skips_null,   -- 643000 / 5  = 128600
       SUM(salary) / COUNT(*) AS avg_over_rows     -- 643000 / 6  = 107166
FROM employees;

-- AVG divides by COUNT(salary)=5, NOT COUNT(*)=6.
-- If you WANT NULLs to count as zero, do it explicitly:
SELECT AVG(COALESCE(salary, 0)) FROM employees;  -- 643000 / 6

So the answer to "what is the average salary?" depends on an unstated assumption: do people with an unknown salary belong in the average or not? AVG says no by default. If your business definition says a missing salary should count as zero, you must say so explicitly with COALESCE. Getting this wrong silently skews every average in a report, which is exactly the kind of quiet error Maya would rather catch before her manager forwards it upstairs.

Maya's next request raises the stakes: "only the departments whose average salary clears 100,000." Both WHERE and HAVING filter, but they act at different stages. The distinction is exactly one word: before or after grouping.

Suppose you want "departments whose average salary exceeds 100,000." The condition is on an aggregate (AVG(salary)), which only exists after grouping, so it must go in HAVING:

SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 100000   -- keep groups, not rows
ORDER BY avg_salary DESC;

 dept_id | avg_salary
---------+------------
      10 |  152500.00
      20 |  116500.00
      30 |  105000.00   -- dept NULL (72000) is excluded

Maya's first instinct is to drop the condition into WHERE, and it fails. At the moment WHERE runs, no grouping has happened, so AVG(salary) is meaningless:

-- ERROR: aggregates are not allowed in WHERE
SELECT dept_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 100000   -- runs at step 2, before grouping!
GROUP BY dept_id;
-- ERROR:  aggregate functions are not allowed in WHERE

The two clauses are not interchangeable, and they are not redundant either. You often use both in one query, and the order matters for both correctness and speed. WHERE trims rows first (so fewer rows get grouped), then HAVING trims the resulting groups:

-- among employees hired after 2019, find departments
-- with more than one such employee
SELECT dept_id, COUNT(*) AS recent_hires
FROM employees
WHERE hire_date > '2019-12-31'   -- row filter, BEFORE grouping
GROUP BY dept_id
HAVING COUNT(*) > 1;            -- group filter, AFTER grouping

By Friday Maya's manager wants the report readable by people who do not memorize department ids. Real reports combine Module 01's joins with this module's grouping. The pattern is everywhere: join the fact table to a lookup table, then group by the human-readable name instead of the cryptic id. Let's report headcount and average salary per department name:

SELECT d.dept_name,
       COUNT(e.emp_id)  AS headcount,
       ROUND(AVG(e.salary), 0) AS avg_salary
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
GROUP BY d.dept_name      -- group by the joined column
ORDER BY avg_salary DESC;

Because this is an inner JOIN, Frank (whose dept_id is NULL) drops out before grouping; there is no department row to match him. Maya notices the headcount is short by one and remembers Frank. If she wants to keep him in a "no department" bucket, she uses a LEFT JOIN and groups by COALESCE(d.dept_name, 'Unassigned'). The choice of join type directly controls which groups exist.

Grouping by multiple columns

You can group by more than one key. Each distinct combination becomes a bucket. For example, count orders per employee per status, a two-dimensional summary:

SELECT emp_id, status,
       COUNT(*)     AS n_orders,
       SUM(amount) AS total
FROM orders
GROUP BY emp_id, status   -- one row per (emp_id, status) pair
ORDER BY emp_id, status;

Subtotals with ROLLUP, a brief look

Sometimes you want per-group totals and a grand total in one result, which is precisely what Maya's manager scribbles at the bottom of the request: "and the company total, please." GROUPING SETS, ROLLUP, and CUBE generate multiple grouping levels at once. You will not need them daily, but recognize the shape:

SELECT dept_id, SUM(salary) AS payroll
FROM employees
GROUP BY ROLLUP(dept_id);   -- per-dept rows + one extra (null) grand-total row

The extra row with a NULL dept_id here is the grand total across all departments. Postgres signals "this is the total, not a real group" with a NULL in the grouped column. That single line answers Maya's whole first week of questions at once. That is all you need to recognize it; the deeper machinery can wait.

Step into Maya's chair. Use the same schema and six employees from Module 00, and add a few orders rows so the grouping has variety. Write each query before peeking; predict the number of output rows first, then run and check.

• Get the total payroll, average salary, and headcount of the whole company in one query. Confirm it returns exactly one row.
• Run COUNT(*), COUNT(dept_id), and COUNT(DISTINCT dept_id) together. Explain out loud why all three numbers differ.
• Average salary per dept_id. Predict how many group rows you get (don't forget Frank's NULL bucket), then verify.
• Try SELECT dept_id, name, AVG(salary) ... GROUP BY dept_id, watch it error, then fix it two ways: with STRING_AGG(name, ', ') and by adding name to GROUP BY.
• Temporarily set one salary to NULL, then compare AVG(salary) against SUM(salary) / COUNT(*). Prove the denominators differ.
• Find departments with average salary above 100,000 using HAVING. Then try the same condition in WHERE and read the error.
• Among employees hired after 2019, find departments with more than one such employee, using one WHERE and one HAVING in the same query.
• Join employees to departments and report headcount + average salary per dept_name. Then switch to a LEFT JOIN with COALESCE so Frank appears in an "Unassigned" bucket.