learning-it by example

#127 SQL and analytics 6.6 min

SQL Mistakes That Give Wrong Numbers

Six made-up orders total 220, but two joins make the same SUM say 505, with no error.

1. Six made-up orders total 220. After joining items and then shipments, what did SUM of the order amount say?
2. What is the fix the video shows for a total that is inflated by joining child tables?
3. In the made-up shop of six orders, two have a null status. A filter status <> 'cancelled' returned 3 orders and 110. What does status IS NOT 'cancelled' return, and why do they differ?
4. Three of the six orders have a null discount. AVG(discount) gave 5.0. Why, and what does the video say is right if a missing discount means no discount?
5. In SQLite, 1 cancelled order out of 6 gives 0 with bad / n and 16 with 100 * bad / n. Which expression gives 16.7?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. Run all four checks

    Copy the code folder first. Run python run.py fanout, python run.py nulls avg and python run.py divide. For each line, write down which number is the correct one and which is the mistake, using the six orders in shop.py.

    A hint

    The orders table has six rows, and its amounts add up to 220.

  2. Fill in the missing statuses

    In shop.py, order 4 and order 6 have status None. Change order 4 to 'paid' and order 6 to 'cancelled'. Predict how the status <> cancelled and status IS NOT cancelled lines change in python run.py nulls, and what python run.py divide prints. On this machine both nulls lines give 4 orders and 140, and the divide share is 33 for 100 * bad / n and 33.3 for the last one.

    A hint

    Once no status is null, the two filters ask the same question.

  3. Take one box away

    In shop.py, remove the box B entry from SHIPMENTS, so order 1 ships in only one box. Predict the row count and total in the join items and boxes line of python run.py fanout. On this machine it now prints 10 | 425, the same as join items.

    A hint

    Each order now has at most one shipment, so the second join no longer multiplies rows.

  4. Give everyone a discount

    In shop.py, give order 2 a discount of 4 instead of None. Predict the two lines of python run.py avg before you run it. On this machine avg(discount) prints 4.75 and the null as zero version prints about 3.1666666666666665. Then explain why the two numbers are still different.

    A hint

    Two orders still have a null discount. Average divides by the number of non null values.

These tasks are for you to try on your own machine. Nothing is checked or saved.

What this does not show

Six made-up orders run only in SQLite; PostgreSQL's IS DISTINCT FROM is mentioned from its docs, not run.

Up next in SQL and analytics

More in SQL and analytics

All SQL and analytics videos

Related videos