GROUP BY — aggregate data by category or value

GROUP BY Splits Rows Into Buckets

GROUP BY tells the database to partition rows into groups sharing the same value(s) in the specified column(s), then apply aggregate functions within each group.

-- Revenue per product category
SELECT
  category,
  COUNT(*)        AS order_count,
  SUM(amount)     AS total_revenue
FROM orders
GROUP BY category
ORDER BY total_revenue DESC;

The Golden Rule of GROUP BY

Every column in SELECT must either appear in GROUP BY or be inside an aggregate function. If you select a column that isn't in GROUP BY and isn't aggregated, the database doesn't know which row's value to show for that column:

-- Error in most databases
SELECT category, name, COUNT(*) FROM orders GROUP BY category;
-- name is not in GROUP BY and not aggregated

-- Correct
SELECT category, COUNT(*) FROM orders GROUP BY category;

Grouping by Multiple Columns

You can group by more than one column. Each unique combination forms a group:

-- Orders per customer per month
SELECT
  customer_id,
  DATE_TRUNC('month', created_at) AS month,
  COUNT(*) AS orders
FROM orders
GROUP BY customer_id, DATE_TRUNC('month', created_at)
ORDER BY customer_id, month;