GROUP BY
Overview
GROUP BY collapses rows that share the same value(s) in the grouping column(s) into a single summary row. Before GROUP BY you have many individual rows; after it, you have one row per unique group. Crucially, WHERE filters individual rows BEFORE GROUP BY collapses them. Any column in SELECT must either appear in the GROUP BY list or be wrapped in an aggregate function (COUNT, SUM, etc.). Against the shared dataset: SELECT status, COUNT(*) AS order_count FROM orders GROUP BY status ORDER BY status; The 8 order rows collapse into 3 groups: cancelled (1), paid (6), pending (1). Think of GROUP BY as putting rows into labelled buckets — one bucket per unique grouping-key value — and then squeezing each bucket down to a single summary row.
Almost every reporting or analytics query needs GROUP BY. Without it, you can only look at individual rows. GROUP BY lets you answer 'how many?', 'what total?', and 'what average?' for each category — the bread and butter of dashboards and business metrics.
Where used: analytics dashboards, summary statistics APIs, report generation
Why learn this
- Writing any query that summarises data — revenue per country, orders per customer, items per category — requires GROUP BY
- Understanding GROUP BY is prerequisite for HAVING, window functions, and virtually all intermediate SQL
Query walkthrough
SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status
ORDER BY status;Focus: Watch 8 individual order rows collapse into 3 group rows — one per distinct status value.
Common mistakes
- Selecting a non-grouped, non-aggregated column: SELECT name, country, COUNT(*) FROM customers GROUP BY country fails because 'name' is not in GROUP BY and not aggregated. Postgres raises 'column must appear in the GROUP BY clause or be used in an aggregate function'. Either add name to GROUP BY or wrap it in an aggregate like MAX(name).
- Forgetting that GROUP BY happens before SELECT aliases: You cannot use a SELECT alias in GROUP BY in standard SQL (Postgres allows it as an extension, but it's not portable). Best practice: repeat the expression or column name in GROUP BY rather than relying on the alias.
- Expecting row-level detail after GROUP BY: Once you GROUP BY country, individual customer rows vanish — you only get one row per country. If you need both the summary AND the detail, use a window function (SUM(...) OVER (...)) instead.
Glossary
- collapse
- To shrink or merge multiple related rows into a single summary row.
- prerequisite
- A foundational concept or skill that you must understand first before you can learn something more advanced.
- aggregate
- A function that takes multiple rows of data and performs a calculation to return a single summarized value.
Recall questions
- What does GROUP BY do to the row set?
- What rule must every column in SELECT follow when GROUP BY is present?
- In the logical query flow, does GROUP BY happen before or after WHERE?
Understanding checks
What rows does this return?
US | 3 AE | 1 JP | 1 MX | 1 UK | 1
WHERE removes Emma (NULL country) first, leaving 7 rows. GROUP BY collapses those 7 rows into 5 groups by country. US has 3 customers (Alice, Bob, Greg); the other 4 countries have 1 each. ORDER BY n DESC puts US first, then the tie-breaking alphabetical order.
What rows does this return?
cancelled | 1 paid | 6 pending | 1
The 8 orders have statuses: paid (orders 1,2,4,6,7,8 = 6), pending (order 3 = 1), cancelled (order 5 = 1). GROUP BY collapses each status into one row, ORDER BY status sorts alphabetically.
Practice tasks
Group by category instead of status
This query counts orders by status. Modify it to count products by category from the products table instead, keeping an ORDER BY on category.
Challenge
Orders per customer
Write a query that shows each customer's name and how many orders they placed. Only include customers who have at least one order. Order by name.
Continue learning
Previous: SELECT, FROM & Columns
Previous: WHERE & Operators
Next: Aggregate Functions
Next: Multi-Column Grouping
Next: OVER & PARTITION BY