Learn/SQL/Querying Data

GROUP BY & Aggregates

Aggregates answer summary questions: how many orders, total revenue, average order size. GROUP BY splits rows into buckets before summarizing.

Beginner~14 min read

Grouping Flow

Rows → groups → aggregate values

Schema Context

users ↔ orders ER
orders samplejson
1| order_id | customer_id | status   | total_amount |
2|----------|-------------|----------|--------------|
3| 1001     | 1           | paid     | 49.99        |
4| 1002     | 1           | shipped  | 12.50        |
5| 1003     | 3           | pending  | 8.00         |
users samplejson
1| user_id | email               | is_active |
2|---------|---------------------|-----------|
3| 1       | [email protected]   | true      |
4| 2       | [email protected]     | false     |
5| 3       | [email protected]   | true      |

Aggregate Functions

FunctionReturnsExample question
COUNT(*)Number of rowsHow many orders?
SUM(col)Total of numeric columnTotal revenue?
AVG(col)Average valueAverage order size?
MIN / MAXSmallest / largestCheapest order?
Total number of ordersjson
1SELECT COUNT(*) AS order_count FROM orders;
Expected outputjson
1| order_count |
2|---|
3| 3 |
Total and average order amountjson
1SELECT
2  SUM(total_amount) AS revenue,
3  AVG(total_amount) AS avg_order
4FROM orders;

GROUP BY

Orders per customerjson
1SELECT customer_id, COUNT(*) AS order_count
2FROM orders
3GROUP BY customer_id;
Expected outputjson
1| customer_id | order_count |
2|---|---|
3| 1 | 2 |
4| 3 | 1 |

SELECT rule

Every column in SELECT must either be in GROUP BY or wrapped in an aggregate. SELECT customer_id, order_id, COUNT(*) is invalid without grouping order_id.

GROUP BY with JOIN

Revenue per user emailjson
1SELECT u.email, SUM(o.total_amount) AS total_spent
2FROM users u
3INNER JOIN orders o ON u.user_id = o.customer_id
4GROUP BY u.email;
Expected outputjson
1| email | total_spent |
2|---|---|
3| [email protected] | 62.49 |
4| [email protected] | 8.00 |

HAVING — Filter Groups

ClauseFiltersExample
WHEREIndividual rows before groupingWHERE status = 'paid'
HAVINGGroups after aggregationHAVING COUNT(*) > 1
Customers with more than one orderjson
1SELECT customer_id, COUNT(*) AS order_count
2FROM orders
3GROUP BY customer_id
4HAVING COUNT(*) > 1;

Common Mistakes

  • Non-aggregated columns missing from GROUP BY.
  • Using WHERE on aggregate results — use HAVING instead.
  • Confusing COUNT(*) with COUNT(column) when NULLs exist.

Practice Prompts

  1. Find the maximum single order amount.
  2. Count orders grouped by status.
  3. Which users spent more than $50 total? (JOIN + GROUP BY + HAVING)

Try These Tools

SQL Query Explainer

Understand GROUP BY queries

Open tool

SQL to CSV

Export aggregate report results

Open tool

Continue Learning

SQL COUNT() Function

COUNT before GROUP BY

SQL CASE Expression

Conditional aggregates

LEFT JOIN

Include users with zero orders

Frequently Asked Questions

What is an aggregate function?
A function that collapses many rows into one value: COUNT, SUM, AVG, MIN, MAX.
When is GROUP BY required?
When SELECT mixes aggregate functions with non-aggregated columns — every selected column must be in GROUP BY or inside an aggregate.
What is the difference between WHERE and HAVING?
WHERE filters rows before grouping. HAVING filters groups after aggregation.
Does COUNT(*) count NULLs?
COUNT(*) counts all rows. COUNT(column) ignores NULL values in that column.
Can I GROUP BY multiple columns?
Yes. Rows are grouped by the combined values of all GROUP BY columns.