Postgres by Example: GROUP BY

GROUP BY splits rows into groups by the value of one or more expressions and then applies aggregates to each group. Without GROUP BY, an aggregate collapses the whole result into one row. With it, you get one row per distinct group — count per customer, sum per month, avg per category. Every non-aggregated column in the SELECT list must appear in GROUP BY (or be functionally dependent on it).

What you'll learn:

• Grouping rows by one or more columns
• Applying aggregates per group
• The rule that non-aggregated columns must be in GROUP BY
• Grouping by expressions, not just columns
• GROUPING SETS, ROLLUP, CUBE for multi-level summaries

-- One row per customer with order count and total
SELECT customer_id,
       count(*)             AS order_count,
       sum(total)           AS total_spent,
       avg(total)::numeric(10,2) AS avg_order
FROM orders_example
GROUP BY customer_id
ORDER BY customer_id;

-- Group by an expression (first letter of fruit name)
SELECT substring(name, 1, 1) AS letter, count(*) AS cnt
FROM fruits
GROUP BY substring(name, 1, 1)
ORDER BY letter;

-- Group by multiple columns
SELECT schemaname, count(*) AS table_count
FROM pg_tables
GROUP BY schemaname
ORDER BY table_count DESC
LIMIT 5;

-- GROUPING SETS: several groupings in one query
SELECT customer_id, count(*) AS orders, sum(total) AS spent
FROM orders_example
GROUP BY GROUPING SETS ((customer_id), ())   -- per-customer rows AND a grand total
ORDER BY customer_id NULLS LAST;

-- ROLLUP: hierarchical subtotals
SELECT customer_id, sum(total) AS spent
FROM orders_example
GROUP BY ROLLUP (customer_id);

The rule is straightforward: in a query with GROUP BY, every column in the SELECT list must either be in the GROUP BY clause or be inside an aggregate. PostgreSQL does honor functional dependency — if id is the primary key, you can GROUP BY id and reference name directly because name is determined by id. But you cannot, say, write SELECT customer_id, total FROM orders GROUP BY customer_id; — total varies within the group.

GROUPING SETS and its shortcuts ROLLUP and CUBE produce multiple groupings in one query — the basis of OLAP-style reports. ROLLUP (a, b, c) produces groupings (a, b, c), (a, b), (a), () — successive subtotals up to a grand total. CUBE (a, b) produces every combination — (a, b), (a), (b), ().

To run (requires orders_example from the joins lesson):

$ psql -f source/group-by.sql postgres
 customer_id | order_count | total_spent | avg_order
-------------+-------------+-------------+-----------
           1 |           2 |       80.00 |     40.00
          99 |           1 |       10.00 |     10.00
(2 rows)
 ...

Common pitfalls:

• "column 'x' must appear in the GROUP BY clause or be used in an aggregate function" — the most common GROUP BY error. Either group by x or wrap it in min(x), max(x), etc.
• WHERE filters rows before grouping; HAVING filters groups after (next lesson). Mixing them up is a common confusion.
• GROUP BY on a nullable column treats all NULLs as one group. If you want them out, add WHERE col IS NOT NULL.
• Grouping by an expression (GROUP BY date_trunc('month', created_at)) is fine, but be aware that PostgreSQL must compute it for every row — index the expression if the query is hot.

Tip: GROUP BY 1, 2 groups by the first and second columns in the SELECT list. It is concise for ad-hoc queries; in committed code, name the columns for clarity.

Try it: Group fruits by the first letter of name: SELECT substring(name, 1, 1) AS letter, count(*) FROM fruits GROUP BY substring(name, 1, 1) ORDER BY letter;. Then try GROUP BY 1. Then add HAVING count(*) > 1 (preview of the next lesson) to see only letters with more than one match.

Source: group-by.sql

Next: HAVING

Home: Postgres by Example