Group by essentials

GROUP BY splits your table into buckets based on column values, then applies aggregate functions to each bucket separately. Want order count per state? GROUP BY customer_state. Revenue per payment type? GROUP BY payment_type.

Group by splits rows into buckets

group_by.sql
sql
-- Orders per customer state
SELECT
  customer_state,
  COUNT(*) AS order_count
FROM customers
GROUP BY customer_state
ORDER BY order_count DESC;

-- Revenue per payment type
SELECT
  payment_type,
  COUNT(*) AS payment_count,
  SUM(payment_value) AS total_revenue
FROM order_payments
GROUP BY payment_type
ORDER BY total_revenue DESC;

GROUP BY collapses rows sharing the same value into one row per group, then aggregates calculate per group.

You can group by more than one column to get finer-grained breakdowns. For example, grouping by both state and city shows you the distribution within each state. The example below also borrows JOIN ... ON to combine tables by matching a shared id column: JOIN customers c ON o.customer_id = c.customer_id attaches each customer to their order, with o and c as short table aliases. Joins get a full deep dive soon; for now just follow the aliases.

multi_group.sql
sql
-- Multi-column GROUP BY: state + payment type
SELECT
  c.customer_state,
  p.payment_type,
  COUNT(*) AS payment_count
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_payments p ON o.order_id = p.order_id
GROUP BY c.customer_state, p.payment_type
ORDER BY c.customer_state, payment_count DESC;

Multi-column GROUP BY creates a bucket for each unique combination. This shows payment preferences by state.

No! Every column in SELECT must either be in the GROUP BY clause or inside an aggregate function (COUNT, SUM, etc.). If you GROUP BY state, there are many cities per state, so SQL would not know which city to show. This is the most common GROUP BY error beginners hit.

Fill in the blanks: Complete the group by query

Loading practice…

Quiz: Quiz

Loading practice…

Code playground: Top 5 categories by revenue

Loading practice…