Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 125 ยท Aggregation

Grouping in Practice

Build real SQL reports with GROUP BY - revenue per month and quarter, top customers and products, percentages of a total, rates and averages of order totals.

You know the mechanics of GROUP BY and HAVING. This lesson puts them to work on the kind of questions a manager actually asks: How much did we sell each month? Who are our best customers? What share of revenue comes from each category? What's our average order value?

Each section builds one report on the shop database, step by step, and points out the traps that make reports quietly wrong: counting rows twice, dividing integers, and averaging the wrong thing. By the end you'll have a toolbox of patterns you can reuse on any data.

A first look at joins

Business data is spread across tables. An order's date and status live in orders, but what was bought, and for how much, lives in order_items. To compute revenue per month you need columns from both, so this lesson uses a few simple joins. You'll study joins properly in the next chapter, starting with Why Joins?. For now, read this pattern:

SQL
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.id

as "for every order, attach each of its item rows". The result has one row per item, and every item row carries the columns of its order. o and i are short table aliases, so o.status means "the status column of orders".

The revenue of one item line is quantity * unit_price. Shipping is charged per order, so it stays in orders.

Revenue per month

Here is the monthly sales report. Cancelled orders are excluded with WHERE, because they never turned into money:

SQL Revenue per month ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

March was the strongest month with 4,514 in revenue from 15 orders; April brought in only 213. Look closely at COUNT(DISTINCT o.id). After the join there's one row per item, so an order with three items appears three times. COUNT(*) would count item lines, not orders. Counting the distinct order ids fixes that. Whenever a join can repeat a row, ask yourself what each row of the joined result represents before you count anything.

Money and floating point. unit_price is stored as a REAL, so sums can come out as 1234.5600000000002. Wrap money totals in ROUND(โ€ฆ, 2) for display. Real systems usually store money as integer cents or as a DECIMAL type; see Data Types.

Per quarter and per weekday

Change the grouping expression and the same query answers a different question. A quarter is the month number divided into blocks of three:

SQL Revenue per quarter ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The integer division (month + 2) / 3 turns months 1โ€“3 into 1, months 4โ€“6 into 2, and so on. Try replacing the quarter expression with strftime('%w', o.ordered_at) (0 = Sunday โ€ฆ 6 = Saturday) to see which weekdays sell best. All the data here is from 2025; with several years you would group by year and quarter.

Missing periods don't appear. A month with no orders produces no group, so a chart built from this query would silently skip it. To show zero rows for empty months, generate a list of months first and join to it. You'll learn how in Recursive CTEs.

Top customers

"Top N" reports combine three clauses: group per thing, sort by the aggregate descending, and keep the first rows with LIMIT. Here are the five customers who spent the most on orders that weren't cancelled:

SQL Top 5 customers by spend ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Two details make this report robust:

  • Group by the id, not by the name. Three customers are called Maya; grouping by first_name alone would merge them into one "super customer". Grouping by the primary key keeps every customer separate.
  • List the other selected columns in GROUP BY too. first_name, last_name and country have only one value per customer, so adding them doesn't change the groups, and the query follows the golden rule in every database.

Ties at the cut-off. LIMIT 5 cuts after exactly five rows, even if the sixth customer spent the same amount as the fifth. When ties matter (prizes, rankings), use RANK() from ROW_NUMBER, RANK and DENSE_RANK instead.

Top products

The same pattern ranks products. This time HAVING also hides products with only a handful of sales, so a single big order can't put a product at the top:

SQL Best-selling products ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The Ultralight Tent leads on revenue because it's expensive, not because it sells often. Sorting by units instead gives a very different list. A good report makes clear which measure it ranks by.

Percentages of a total

A percentage needs two numbers: the group's value and the grand total. The simplest way to get the total is a small subquery in parentheses, which runs once and returns a single value (you'll learn more in Subqueries):

SQL Share of orders by status ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Delivered orders are 47.5% of all 120 orders and cancelled ones 14.2%. Notice the 100.0. In SQLite (and in PostgreSQL and SQL Server) dividing one integer by another throws away the fractional part: 57 / 120 is 0, so 100 * 57 / 120 gives 47, and 57 / 120 * 100 gives 0. Starting the calculation with the real number 100.0 makes the whole expression use decimal arithmetic. MySQL and Oracle return decimals for / anyway, which is one more reason to write the 100.0 explicitly.

With a window function

Window functions, covered in their own chapter, offer a neater way to get the total: SUM(โ€ฆ) OVER () adds up a column across all result rows. Because window functions run after grouping, you can even wrap an aggregate inside one. Here is each category's share of revenue:

SQL Revenue share per category ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SUM(i.quantity * i.unit_price) is the revenue of one category; SUM(SUM(โ€ฆ)) OVER () is the sum of those category revenues, which is total revenue. The percentages add up to 100 (give or take rounding).

Rates: the average of a condition

A rate is a percentage of rows that meet a condition: cancellation rate, conversion rate, on-time rate. In SQLite a comparison like status = 'cancelled' returns 1 or 0, so its average is the share of rows where it's true:

SQL Cancellation rate per quarter ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SUM(condition) counts the matching rows and AVG(condition) gives their share. The portable spelling, which works in every database, is SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END). This idea of aggregating a condition is the heart of the pivot tables lesson.

Average order value: aggregate twice

"What's our average order value?" sounds like one AVG, but it's a trap. AVG(i.quantity * i.unit_price) averages item lines, not orders. You first need one total per order, and then the average of those totals. That's two levels of grouping, which you can write by putting one query inside the FROM of another:

SQL Average order value ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The inner query returns one row per order with its total; the outer query treats that result like a table and averages it. The 103 orders that weren't cancelled are worth 257.44 on average, while the average item line is only 104.39, because most orders contain several lines. The same "group, then aggregate the groups" shape answers questions such as "average number of orders per customer" or "the busiest day's sales". Common Table Expressions let you write it more readably with WITH.

A checklist for correct reports

Before you trust a grouped report, run through these questions:

  1. What does one row of the FROM represent? After joins it may be an item, not an order. Count with COUNT(DISTINCT โ€ฆ) when rows repeat.
  2. Which rows belong in the report? Filter cancellations, test accounts or date ranges in WHERE.
  3. Am I grouping by a unique key? Group by ids, then add display columns to GROUP BY.
  4. Is any division done in integers? Use 100.0 or CAST(โ€ฆ AS REAL).
  5. Am I averaging the right thing? Aggregate per entity first when the question is "per order" or "per customer".
  6. Is the order explicit? Add ORDER BY, and think about ties when using LIMIT.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. After orders JOIN order_items, what does COUNT(*) count?

    The join produces one row per item line, and each order repeats once per item. Use COUNT(DISTINCT o.id) to count orders.
  2. 2. In SQLite, what does SELECT 100 * 17 / 120; return?

    All three numbers are integers, so the division drops the fraction. 1700 / 120 is 14 in integer arithmetic. Write 100.0 to get 14.17.
  3. 3. Why should a "top customers" report group by c.id rather than by c.first_name?

    Group by a unique key so every customer stays separate, then add the display columns to GROUP BY.
  4. 4. In SQLite, what does AVG(status = 'cancelled') compute?

    The comparison yields 1 or 0 for every row, so the average is the fraction of rows where it is true.
  5. 5. How do you compute the average order value correctly?

    The first and third options average item lines. You need one total per order first, then the average of the totals.

Summary

  • Reports often need a join; first decide what one row of the joined data represents, and use COUNT(DISTINCT id) when rows repeat.
  • Group by date expressions (strftime, quarters) for time-based reports; empty periods don't appear by themselves.
  • Top-N reports = GROUP BY the key + ORDER BY the aggregate DESC + LIMIT.
  • Percentages need a total: a scalar subquery or SUM(SUM(x)) OVER (). Multiply by 100.0 to avoid integer division.
  • SUM(condition) counts matches and AVG(condition) gives a rate (portable form: CASE WHEN โ€ฆ THEN 1 ELSE 0 END).
  • For "average per order" questions, aggregate per order first, then aggregate the results.

Next: Subtotals: ROLLUP, CUBE and GROUPING SETS, where you'll add subtotal and grand-total rows to your reports.