GROUP BY

GROUP BY is how SQL turns one big pile of rows into several smaller piles, then runs aggregates on each pile. Instead of one total for the whole menu, you can ask for one total per category, one count per stock state, or one summary per price band.

The trade-off is real: GROUP BY folds rows into summaries. That is exactly what you want for reports, and exactly why the original detail rows are no longer available in the result.

Split, apply, combine

In 4.1, an aggregate without GROUP BY collapsed all selected rows into one answer row. Add GROUP BY category, and PostgreSQL splits the menu into one pile per category before applying the aggregates:

SELECT category,
       COUNT(*) AS items,
       SUM(price) AS total_price,
       MIN(price) AS cheapest,
       MAX(price) AS priciest
FROM menu_items
GROUP BY category
ORDER BY category;

 category | items | total_price | cheapest | priciest
----------+-------+-------------+----------+----------
 coffee   |     4 |       14.80 |     2.50 |     4.50
 drinks   |     1 |        2.20 |     2.20 |     2.20
 pastry   |     3 |       10.90 |     3.20 |     4.10
 tea      |     2 |        6.40 |     3.00 |     3.40
(4 rows)

Ten menu rows went in. Four summary rows came out, because there are four category piles. That is the one-row-per-group law: the result has one row for each distinct grouping value.

The grain of the result

The grain of a result means what one row represents. In the first query, one row means "one category." If you group by two columns, one row means "one unique combination of both columns."

SELECT category,
       in_stock,
       COUNT(*) AS items,
       MIN(price) AS cheapest,
       MAX(price) AS priciest
FROM menu_items
GROUP BY category, in_stock
ORDER BY category, in_stock DESC;

 category | in_stock | items | cheapest | priciest
----------+----------+-------+----------+----------
 coffee   | t        |     3 |     2.50 |     4.00
 coffee   | f        |     1 |     4.50 |     4.50
 drinks   | t        |     1 |     2.20 |     2.20
 pastry   | t        |     3 |     3.20 |     4.10
 tea      | t        |     1 |     3.00 |     3.00
 tea      | f        |     1 |     3.40 |     3.40
(6 rows)

The result is no longer "one row per category." It is "one row per category-and-stock-state." Coffee appears twice because stocked coffee and sold-out coffee are different groups. Pastry appears once because every pastry is in stock.

When a grouped query surprises you, read the selected non-aggregate columns out loud. They tell you the grain.

Grouping by expressions

Groups do not have to be raw columns. They can be expressions, including the CASE labels you learned in 3.3. Here the grouping expression creates price bands:

SELECT CASE
         WHEN price < 3 THEN 'under 3'
         WHEN price < 4 THEN '3 to 3.99'
         ELSE '4 and up'
       END AS price_band,
       COUNT(*) AS items,
       MIN(price) AS cheapest,
       MAX(price) AS priciest
FROM menu_items
GROUP BY CASE
           WHEN price < 3 THEN 'under 3'
           WHEN price < 4 THEN '3 to 3.99'
           ELSE '4 and up'
         END
ORDER BY MIN(price);

 price_band | items | cheapest | priciest
------------+-------+----------+----------
 under 3    |     2 |     2.20 |     2.50
 3 to 3.99  |     5 |     3.00 |     3.80
 4 and up   |     3 |     4.00 |     4.50
(3 rows)

Each row now represents one price band. The CASE expression runs for every menu item, assigns a label, and GROUP BY piles together rows with the same label.

Why loose columns are rejected

Now try to sneak a detail column into a grouped summary:

SELECT category, name, COUNT(*) AS items
FROM menu_items
GROUP BY category;

ERROR:  column "menu_items.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT category, name, COUNT(*) AS items
                         ^

That error is PostgreSQL protecting you from a nonsense result. There is one coffee group, but four coffee names. Which name should appear beside the coffee count: Espresso, Cappuccino, Flat White, or Cold Brew? SQL needs every selected expression to have exactly one value per output row.

So each selected expression must be one of two things:

• a grouping key, like category, because every row in that output group shares it
• an aggregate, like COUNT(*), because it deliberately combines many values into one

MySQL is the famous caveat here: permissive configurations have allowed non-grouped columns and picked a value for you. Treat that as silent nondeterminism, not as a feature. A grouped report should say exactly why each output cell has one value.

The NULL paradox

In comparisons, NULL = NULL is not true; 3.2 taught that the result is unknown. But GROUP BY is not asking whether two unknown values are equal in a filter. It is filing rows into summary buckets, and all NULLs for the same grouped expression go into one bucket.

The staff table has two undecided bonuses:

SELECT bonus,
       COUNT(*) AS staff_rows
FROM staff
GROUP BY bonus
ORDER BY bonus NULLS LAST;

 bonus  | staff_rows
--------+------------
   0.00 |          1
 120.00 |          1
 250.00 |          1
 400.00 |          1
   NULL |          2
(5 rows)

Rosa and Tomas do not have equal bonuses in the normal comparison sense. Their bonuses are unknown. But for grouping, "unknown bonus" is one summary bucket, so the NULL group has two rows.

Try the grouping patterns in the playground. Change the grouping columns and watch the footer; it tells you how many groups your result grain produced.

GROUP BY gives you summary rows. The next lesson teaches HAVING, the clause that keeps or removes whole groups after those summaries exist.