GROUP BY

Group rows by one or more columns and apply aggregates to each group.

Overview

GROUP BY partitions rows into groups based on column values and applies aggregate functions to each group independently. Every column in the SELECT list must either appear in the GROUP BY clause or be inside an aggregate function otherwise PostgreSQL raises an error. This rule enforces that each output row represents a unique group. Common use cases: total sales per month, average rating per product, count of users per country. GROUP BY can reference multiple columns for finer grouping (e.g., GROUP BY year, month for monthly data).

Key Points

•GROUP BY col groups all rows with the same col value together
•Every non-aggregate SELECT column must be in GROUP BY
•Can group by multiple columns: GROUP BY a, b
•GROUP BY processes AFTER WHERE but BEFORE HAVING
•NULL values are grouped together (treated as equal for grouping)

Examples

Total and count per category

SELECT
  category,
  COUNT(*)       AS product_count,
  SUM(price)     AS total_value,
  AVG(price)     AS avg_price
FROM products
GROUP BY category
ORDER BY product_count DESC;

Group by multiple columns

SELECT
  EXTRACT(YEAR FROM order_date)  AS year,
  EXTRACT(MONTH FROM order_date) AS month,
  SUM(total)                     AS monthly_revenue
FROM orders
GROUP BY year, month
ORDER BY year, month;

GROUP BY

Overview

Key Points

Examples

Exercises

Knowledge Check

You write: SELECT department, name, COUNT(*) FROM employees GROUP BY department. What happens?