Lesson 3 of 7 · 55 min

Calculate grouped metrics and check the denominator

Learn the reasoning. Try the task. Compare your answer.

Prepared for DataCareerHub · Curriculum version 2026-10-15.3

By the end of this lesson

Build monthly count, sales and AOV at order grain.
Distinguish row filters in WHERE from group filters in HAVING.
Avoid integer-division and ungrouped-column mistakes.

Use the practice filesReady-to-open SQLite database ↓14 practice starters ↓Worked SQL solutions ↓Setup & data dictionary ↓All datasets & instructions ↗

SQLite syntax · Read-only exercises run on your local practice copy · Monetary inputs are integer cents, converted to EUR in the query outputs.

Understand the method

Aggregation compresses many rows into one summary row per group. That changes the output grain. A monthly summary represents a month, not an individual order. Define the input population before grouping and give every output a unit and meaning. A well-labelled table should let another analyst reconstruct both numerator and denominator.

Count the intended records

COUNT(*) counts rows. COUNT(column) counts non-NULL values of that expression. COUNT(DISTINCT order_id) counts distinct non-NULL order identifiers. For verified orders, the primary key and non-NULL identifier mean each row is one order. In a joined result, that assumption may no longer hold; the next lesson demonstrates why.

Filter to completed orders before computing completed sales and AOV. Returned and cancelled orders remain in the source but are outside this metric population. Do not include their counts in the AOV denominator or subtract their values again from a total that already excludes them. This case definition is not a general accounting-revenue policy.

Begin with one aggregate

SELECT COUNT(*) AS completed_orders,
       SUM(order_value_cents) / 100.0 AS sales_eur
FROM orders
WHERE status = 'completed';

The result is eight orders and €1,100 across the two months. Adding substr(order_date, 1, 7) as a month key and grouping by that expression creates two monthly rows. This substring operation is justified by the verified ISO date convention. Reusing it on unverified or differently formatted strings would not be reliable date handling.

Ratios must use matching populations

AOV is completed sales divided by completed-order count. In the grouped query use SUM(order_value_cents) / (100.0 * COUNT(*)). The 100.0 converts cents and produces a real-valued calculation in SQLite. SQLite can perform integer division when both operands are integers: 1 / 2 is not the same result as 1.0 / 2. Never assume the display format will repair a truncated calculation.

Each existing completed group contains at least one row, so COUNT(*) is positive within these groups. If you build a separate month grid that includes empty groups, guard the denominator with NULLIF(count, 0). SUM over no matching values and a genuine sum of zero convey different information. Decide how your reporting contract handles each rather than blanketing everything with zero.

WHERE and HAVING answer different questions

WHERE filters individual source rows before grouping. HAVING filters grouped results. To find channels with at least €550 of completed sales, use WHERE for completed status, GROUP BY channel, then HAVING for the sum threshold. The comparison is to 55000 when the expression remains in cents. Comparing to 550 would ask a €5.50 question.

Select only grouping keys and appropriate aggregates in the summary. SQLite can accept some bare, non-grouped columns, but they can supply misleading values and are not a sound portable reporting pattern. Do not select order_id in a monthly aggregate simply because your local engine permits it. Use ORDER BY month to define the presentation sequence.

Your turn

Practical exercise

Complete Q5 and Q6. Q5 returns month, completed-order count, completed sales EUR and AOV EUR. Q6 returns channels whose combined completed sales are at least €550. Show the threshold in the correct stored units. Independently reconcile the two monthly sales totals to the all-period total and explain why averaging the two monthly AOVs is safe in this particular sample but not a universal method.

Deliverable: two grouped queries, a reconciliation and an explanation of the denominator. Do not change the fixture to make a result match.

Open the worked solution

SELECT substr(order_date, 1, 7) AS month,
       COUNT(*) AS completed_orders,
       SUM(order_value_cents) / 100.0 AS sales_eur,
       SUM(order_value_cents) / (100.0 * COUNT(*)) AS aov_eur
FROM orders
WHERE status = 'completed'
GROUP BY substr(order_date, 1, 7)
ORDER BY month;

February has 4 completed orders, €500 sales and €125 AOV. March has 4, €600 and €150. Their sales sum to €1,100 and counts to eight. Overall AOV is €1,100 / 8 = €137.50. An unweighted average of €125 and €150 happens to agree because both months have the same order count. With unequal counts, calculate the overall ratio from combined sales and orders.

SELECT channel, SUM(order_value_cents) / 100.0 AS sales_eur
FROM orders
WHERE status = 'completed'
GROUP BY channel
HAVING SUM(order_value_cents) >= 55000
ORDER BY channel;

store and web each return €550. HAVING compares the cents sum to 55000. Status belongs in WHERE because it defines the rows contributing to each group; moving it into an arbitrary ungrouped expression would not define the same calculation.

Check your understanding

One more question.

SQL references: SELECT and joins, aggregate functions, window functions.

How these learning materials are prepared and maintained · Report a correction: support@datacareerhub.io

Read and practice without an account. Sign in with an existing account to save lesson completion.

Next lesson →

Fictional training data. Portfolio practice is not a claim of client work or an accredited qualification. Explore practical lessons, worked examples and projects. Follow a published path, practise the method and build work you can explain.