GROUP BY in SQL — explained with real-world examples

GROUP BY collapses many rows that share the same value into one row, so aggregate functions (SUM, COUNT, AVG, MAX, MIN) can summarize each group.

Basic example

-- orders table: id, customer_id, total, status, created_at

-- Total revenue per customer
SELECT customer_id, SUM(total) AS revenue
FROM orders
GROUP BY customer_id
ORDER BY revenue DESC
LIMIT 10;

Multi-column GROUP BY

-- Daily orders per status
SELECT
  DATE(created_at)  AS day,
  status,
  COUNT(*)          AS orders,
  SUM(total)        AS revenue
FROM orders
GROUP BY DATE(created_at), status
ORDER BY day DESC, status;

The cardinal rule

Every column in the SELECT list must be either:

• In the GROUP BY clause, or
• Inside an aggregate function

-- ❌ name is neither grouped nor aggregated
SELECT customer_id, name, SUM(total) FROM orders GROUP BY customer_id;

-- ✅ name added to GROUP BY
SELECT customer_id, name, SUM(total)
FROM orders JOIN customers ON customers.id = orders.customer_id
GROUP BY customer_id, name;

-- ✅ or wrapped — MAX is a no-op since name is the same per customer
SELECT customer_id, MAX(name), SUM(total) FROM ... GROUP BY customer_id;

Real interview-grade example — top 3 customers per month

SELECT month, customer_id, revenue
FROM (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    customer_id,
    SUM(total) AS revenue,
    RANK() OVER (
      PARTITION BY DATE_TRUNC('month', created_at)
      ORDER BY SUM(total) DESC
    ) AS rk
  FROM orders
  GROUP BY 1, 2
) t
WHERE rk <= 3
ORDER BY month DESC, rk;

GROUP BY + window function gets you "top N per group" — a very common reporting need.

Performance — what an index does for GROUP BY

CREATE INDEX idx_orders_customer ON orders(customer_id);

For GROUP BY customer_id, the database can scan the index in order and emit one row per customer-id boundary — no sort step needed. On a 50M-row table, this can be the difference between 2 seconds and 2 minutes.

Gotcha — NULL groups

NULL values form their own group. If you don't want them:

SELECT category, COUNT(*) FROM products WHERE category IS NOT NULL GROUP BY category;