Lesson 4 of 7 · 60 min

Join tables without multiplying your sales

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

Prepared for DataCareerHub · Curriculum version 2026-10-15.3

By the end of this lesson

Predict join cardinality before summing order-level values.
Aggregate line items to order grain and reconcile to orders.
Preserve empty channels with a correctly filtered LEFT JOIN.

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

A join combines matching rows, not business meanings. An order joined to two item rows appears twice in the joined result. If you then sum its order-level value, you count that amount twice. The syntax can be perfectly valid while the metric is wrong. Predict the relationship and resulting grain before choosing an aggregate.

See the row multiplication

orders has 12 rows and order_items has 24, with exactly two lines for every order. An INNER JOIN on order_id produces one row per matched line. For completed orders it produces 16 line rows representing eight distinct orders. Count before and after the join, then compare summed order values to the order-only baseline.

COUNT(DISTINCT o.order_id) can repair an order count in this specific joined population, but it does not repair the sum of repeated order amounts. SUM(DISTINCT order_value_cents) is not a general repair either: different orders can legitimately have equal values. In this case F001 and M003 both have €100, and F004 and M004 both have €200. Across all completed orders, summing distinct amounts would lose real sales.

Bring the detail to a compatible grain

Multiply each item quantity by its unit price in cents, sum by order_id, and join that one-row-per-order result to orders. This creates a meaningful reconciliation between two representations of the same amount. Query Q8 returns only missing or mismatched item totals. For the verified fixture it should return no rows. An empty exception report means that this check found no exceptions, not that all possible data-quality conditions are proven.

The item records are synthetic teaching additions. Each order has one line with quantity two and another with quantity one; their extended values sum to the recorded order value. The data dictionary documents this construction. It is not evidence about actual product mix or customer behaviour.

LEFT JOIN and missing matches

The channels dimension includes partner, which has no orders. Begin with channels and LEFT JOIN orders if you want to retain all three channels. Put o.status = 'completed' in the ON condition so unmatched channels remain in the result. A WHERE condition requiring a matched completed order would remove the NULL-extended partner row.

For partner, COUNT(*) would count the preserved dimension row, giving one even though there are no matched orders. COUNT(o.order_id) correctly gives zero because the joined identifier is NULL. COALESCE(SUM(o.order_value_cents), 0) is an intentional reporting choice here: the complete channel list and covered period define partner as zero activity in the supplied sample. That does not mean every missing measurement in every dataset should become zero.

Test the relationship, not just a total

The channels key is unique and verified orders reference it. The items table has a unique line ID and an order foreign key. Constraints help protect these stated relationships, but an SQLite connection must enable foreign-key enforcement for future writes; the setup script does so. Check both row counts and reconciliations. Two inflated summaries can agree with each other if they share the same join mistake.

Your turn

Practical exercise

Complete Q7–Q9. Q7 deliberately demonstrates the inflated completed-sales result after a line-level join. Q8 returns only missing or mismatched order/item totals after pre-aggregation. Q9 reports every channel, completed-order count and euro sales, including partner. Explain why COUNT(DISTINCT order_id) alone does not fix Q7, and why moving the completed filter from ON to WHERE changes Q9.

Deliverable: three queries and a grain note describing the output row meaning of each. As an independent extension, calculate all-period SUM(DISTINCT order_value_cents) for completed orders and explain its incorrect €800 result.

Open the worked solution

SELECT substr(o.order_date, 1, 7) AS month,
       COUNT(*) AS joined_rows,
       COUNT(DISTINCT o.order_id) AS distinct_orders,
       SUM(o.order_value_cents) / 100.0 AS inflated_sales_eur
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY substr(o.order_date, 1, 7)
ORDER BY month;

Q7 reports eight joined rows, four distinct orders and €1,000 for February; eight, four and €1,200 for March. Both sales values are doubled. DISTINCT repairs the count only. SUM(DISTINCT amounts) removes different orders that happen to share amounts, producing €800 across all completed orders instead of €1,100.

WITH item_totals AS (
    SELECT order_id, SUM(quantity * unit_price_cents) AS item_total_cents
    FROM order_items
    GROUP BY order_id
)
SELECT o.order_id, o.order_value_cents, t.item_total_cents
FROM orders AS o
LEFT JOIN item_totals AS t ON t.order_id = o.order_id
WHERE t.order_id IS NULL OR t.item_total_cents <> o.order_value_cents
ORDER BY o.order_id;

Q8 returns zero exceptions. The child aggregation has one row per order before the join.

SELECT c.channel,
       COUNT(o.order_id) AS completed_orders,
       COALESCE(SUM(o.order_value_cents), 0) / 100.0 AS sales_eur
FROM channels AS c
LEFT JOIN orders AS o
  ON o.channel = c.channel AND o.status = 'completed'
GROUP BY c.channel
ORDER BY c.channel;

partner has 0 completed orders and €0; store has 4 and €550; web has 4 and €550. COUNT(o.order_id) counts actual matches. The completed predicate in ON preserves partner, while a WHERE predicate on the unmatched order would exclude it.

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.