GROUP BY
GROUP BY groups rows that share the same value in specified columns, then applies aggregate functions to each group. This is the core of data summarization - total sales per customer, product count per category, orders per month.
GROUP BY Rules
- Every column in SELECT must appear in GROUP BY - unless it's an aggregate
- Aggregate functions (COUNT, SUM, etc.) are applied per group
- GROUP BY runs before SELECT and HAVING, after WHERE
- Can GROUP BY multiple columns - groups are unique combinations
- Order of results is not guaranteed - use ORDER BY to sort
GROUP BY Examples
-- Orders per user (how many orders each customer placed)
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id;
-- user_id=1 → 2 orders, user_id=2 → 1 order
-- Total spent per user
SELECT
u.name,
COUNT(o.id) AS num_orders,
SUM(o.total_amount) AS total_spent
FROM orders o
JOIN users u ON o.user_id = u.id
GROUP BY u.id, u.name;
-- Revenue by payment method
SELECT
payment_method,
COUNT(*) AS num_payments,
SUM(amount) AS total_revenue
FROM payments
GROUP BY payment_method;
-- Product price stats (all in one grouped query)
SELECT
CASE WHEN price < 100 THEN 'Budget' ELSE 'Premium' END AS tier,
COUNT(*) AS product_count,
AVG(price) AS avg_price
FROM products
GROUP BY CASE WHEN price < 100 THEN 'Budget' ELSE 'Premium' END;
-- Orders grouped by status
SELECT status, COUNT(*) AS count, SUM(total_amount) AS revenue
FROM orders
GROUP BY status
ORDER BY revenue DESC;Quick Quiz
Tip
Tip
Practice GROUP BY in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
GROUP BY collapses rows by a column - aggregates summarize each group
Common Mistake
Warning
A common mistake with GROUP BY is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of GROUP BY from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- GROUP BY groups rows that share the same value in specified columns, then applies aggregate functions to each group.
- Every column in SELECT must appear in GROUP BY - unless it's an aggregate
- Aggregate functions (COUNT, SUM, etc.) are applied per group
- GROUP BY runs before SELECT and HAVING, after WHERE