Overview
GROUP BY collapses rows into buckets. COUNT, SUM, AVG, MIN, and MAX summarize each bucket.
On this page7 sections
The table we keep using
Source data
Five orders with three different statuses. We want to summarize each status group.
| order_id | order_status | order_total |
|---|---|---|
| O0001 | paid | 149.00 |
| O0002 | cancelled | 42.50 |
| O0003 | paid | 88.00 |
| O0004 | pending | 210.75 |
| O0005 | paid | 55.20 |
Sometimes you do not want to see individual rows. Instead, you want a summary: how many orders exist, what is the total revenue, or what is the average order size. Aggregate functions collapse many rows into a single summary value.
The five core aggregate functions are: COUNT (counts rows), SUM (adds up values), AVG (calculates the mean), MIN (finds the smallest value), and MAX (finds the largest value). These functions take a column as input and return one number.
GROUP BY is the companion to aggregation. It splits your data into buckets based on a column's values, then applies the aggregate function to each bucket separately. Without GROUP BY, the entire table is treated as one giant bucket.
What we need from it
An e-commerce company needs to answer questions like: 'What is our gross merchandise value (GMV) per order status?' or 'How many events came from each country?' These questions require counting or summing across groups of rows. You cannot answer them by looking at individual rows.
If you forget GROUP BY when using an aggregate, the database either throws an error or silently returns a meaningless result. Understanding when and why to group is the bridge between reading raw data and producing business metrics.
The grain check is essential: before writing any aggregate, state what one result row represents. 'One row per order_status' or 'one row per country' defines the grain of your result.
Trace it step by step
When you write GROUP BY order_status, the engine sorts all rows into buckets: one bucket for 'paid', one for 'cancelled', one for 'pending'. Then it applies aggregate functions (COUNT, SUM, etc.) within each bucket and returns one row per bucket.
Each bucket becomes one row in the result after aggregate functions run.
Same table, next cut
Example 1: COUNT and SUM by group
Run the example below in this tab. Read the input, follow the code, then check the output matches what you expect.
SELECT
order_status,
COUNT(*) AS order_count,
ROUND(SUM(order_total), 2) AS gmv
FROM orders
GROUP BY order_status
ORDER BY gmv DESC;Result: one row per status. The 3 paid orders total 292.20 in revenue.
| order_status | order_count | gmv |
|---|---|---|
| paid | 3 | 292.20 |
| pending | 1 | 210.75 |
| cancelled | 1 | 42.50 |
Every non-aggregated column in SELECT must also appear in GROUP BY. That is because the database needs to know which bucket each column belongs to. If you select order_status and COUNT(*), the database needs GROUP BY order_status to know where to place each count.
Example 2: COUNT variations
COUNT has three forms that do very different things:
Three different COUNTs give three different answers from the same data.
| Expression | What it counts | NULL handling |
|---|---|---|
| COUNT(*) | Every row in the group | Counts rows even if all columns are NULL |
| COUNT(column) | Rows where that column is not NULL | Skips NULL values |
| COUNT(DISTINCT column) | Unique non-NULL values | Skips NULL, removes duplicates |
SELECT
country,
COUNT(*) AS total_events,
COUNT(DISTINCT user_id) AS unique_users
FROM ecommerce_events
GROUP BY country
ORDER BY total_events DESC;Result: the US has the most events, but the ratio of events to users differs by country.
| country | total_events | unique_users |
|---|---|---|
| US | 1250 | 340 |
| UK | 890 | 215 |
| DE | 430 | 128 |
Example 3: HAVING filters groups
WHERE filters individual rows before groups are formed. HAVING filters groups after aggregation is complete. This distinction matters. If you want 'only statuses with at least 5 orders', you must use HAVING because the count does not exist until after grouping.
-- WRONG: WHERE cannot use aggregate functions
-- SELECT order_status, COUNT(*) AS n
-- FROM orders
-- WHERE COUNT(*) >= 2 -- ERROR!
-- GROUP BY order_status;
-- CORRECT: HAVING filters after aggregation
SELECT
order_status,
COUNT(*) AS order_count,
SUM(order_total) AS gmv
FROM orders
GROUP BY order_status
HAVING COUNT(*) >= 2
ORDER BY gmv DESC;Result: only 'paid' has 2 or more orders, so it is the only surviving group.
| order_status | order_count | gmv |
|---|---|---|
| paid | 3 | 292.20 |
WHERE vs HAVING
WHERE filters rows before groups form. HAVING filters groups after aggregation. Mixing them up is the most common grouping bug. If your condition involves an aggregate like COUNT or SUM, use HAVING.
AVG ignores NULL
AVG only divides by the count of non-NULL values. If 10 rows have values and 5 are NULL, AVG divides the sum by 5, not 10. This can produce unexpectedly high averages.
State the grain first
Before writing any GROUP BY, say out loud: 'One result row represents ___.' This prevents accidentally mixing grains or summing the wrong thing.
A high count does not guarantee the highest GMV. Check both.
Every aggregate function skips NULL except COUNT(*).
| Function | What it measures | NULL behavior | Typical alias |
|---|---|---|---|
| COUNT(*) | Total rows in group | Counts every row | order_count |
| COUNT(col) | Rows with non-NULL value | Skips NULL | paid_count |
| COUNT(DISTINCT col) | Unique non-NULL values | Skips NULL + deduplicates | unique_users |
| SUM(col) | Additive total | Skips NULL | gmv |
| AVG(col) | Mean (sum/count of non-NULL) | Skips NULL | avg_order |
| MIN(col) | Smallest value | Skips NULL | first_order |
| MAX(col) | Largest value | Skips NULL | latest_order |
Copy-paste without reading the output
Run Sample first. If the numbers or row count look wrong, stop and re-read the previous section before changing code.
Common beginner questions
Why do I need GROUP BY with aggregate functions?
Without GROUP BY, the aggregate runs across the entire table and returns one row. GROUP BY tells the engine to produce one row per group. If you select a non-aggregated column without grouping, the database cannot decide which value to show.
Can I GROUP BY multiple columns?
Yes. GROUP BY country, device creates one row per unique country-device pair. This is a finer grain than grouping by country alone and will produce more rows.
What is the difference between ROUND(SUM(...), 2) and SUM(ROUND(..., 2))?
Rounding before summing can accumulate rounding errors across many rows. It is safer to sum first, then round the result once.
What comes next
Aggregation summarizes rows within one table. But real analysis usually needs data from multiple tables combined. The next lesson covers JOINs: how to connect orders to their payments, items, or customer details.
Practice
Run Sample to group events by country. Then complete the exercise: from orders, group by order_status and return status, order count, and SUM of order_total as gmv. Only keep groups with at least 5 orders.
Practicals · load into the editor
After you read the theory, run these in the pane on the right. They execute in this tab, no cluster.
Practice this
Same ideas as interview drills. These challenges open in the studio with a dataset and tests already set up.
- Departments with more than three peopleInterview-style drill: HAVING filter for departments larger than three employees.Studiobeginnersql8 min
- Employee salary aggregatesInterview-style drill: Return count, average, min, and max salary across all employees.Studiobeginnersql8 min
- Conditional category revenueInterview-style drill: SUM CASE WHEN for Electronics and Clothing revenue in one row.Studiointermediatesql12 minPro