Grouping rows with GROUP BY

Run:

You should see:

The categories repeat across different rows:

Instead of treating all products as one set, we can divide them into groups based on their category.

Run:

You should see:

PostgreSQL divided the rows according to category.

Then it ran:

separately for each group.

Conceptually:

Individual rows fall into buckets, and each bucket produces one result row:

Without grouping:

we get one summary:

With:

we get one summary for every distinct category.

A useful mental model is:

The same idea works with our orders table.

Run:

You should see:

Instead of counting all 100000 orders together, PostgreSQL calculates a separate count for each status.

We are not limited to COUNT().

For example:

You should see:

Each aggregate is calculated independently for every status group.

Conceptually:

Consider:

status can appear in the SELECT list because it is the grouping column.

Within each group, every row has the same status.

For example:

so PostgreSQL knows which value to return for the group.

Now consider:

PostgreSQL rejects this:

For a status such as delivered, there are many different total_amount values.

PostgreSQL cannot choose one arbitrary value to represent the whole group.

The general rule is:

A selected value in a grouped query normally needs to either be part of the grouping or be calculated by an aggregate function.

For example:

is valid because MAX() reduces all the different amounts in each group to one value.

There is an important PostgreSQL exception to the general rule.

If the grouped columns include a table's primary key, PostgreSQL knows that other columns from that same table are determined by that primary key.

For example:

is allowed because:

is the primary key.

You should see:

A particular id identifies exactly one customer, so full_name and email are determined by it.

You do not need this exception for most aggregation queries, but it explains why PostgreSQL may accept some queries that appear to select columns not explicitly listed in GROUP BY.

A group can be defined by more than one column.

For example, suppose we want an order count for each status in each year.

Run:

You should see:

PostgreSQL created groups based on each distinct combination:

A row belongs to the group defined by both values.

Four years and five statuses produce twenty groups, so the query returns twenty rows.

Suppose we want to count only orders that are not delivered.

Run:

You should see:

The logical flow is:

WHERE decides which individual rows are available to be grouped.

Then GROUP BY divides the remaining rows.

Then aggregate functions calculate values for those groups.

Notice there is no delivered row at all in the result. The group was never formed, because no delivered row survived WHERE.

Consider:

Even though PostgreSQL groups rows by category, that does not mean the final rows are guaranteed to appear alphabetically.

Grouping and sorting are different operations.

If you need a particular output order, write it explicitly:

A useful distinction is:

If a grouping column contains NULL, PostgreSQL groups those NULL values together for grouping purposes.

Imagine:

Then:

produces one group for the rows whose category is NULL.

So grouping does not create a separate group for every individual NULL row.

Suppose we run:

and now want only statuses with more than 10000 orders.

We cannot use:

because WHERE runs before the groups and their counts exist.

To filter based on an aggregate result, PostgreSQL provides another clause:

The important point is:

`GROUP BY` divides rows into groups so aggregate functions can calculate a separate result for each group.