learning-it by example

Back to the video

SQL Mistakes That Give Wrong Numbers

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

Video #127 ยท SQL and analytics

Steps in the video

  1. 0:00 Same six orders: 220 in sales, or 505 after two joins
  2. 0:36 The idea
  3. 1:00 Setup
  4. 1:16 A small shop with traps
  5. 1:44 The loop
  6. 1:57 Fan-out joins
  7. 2:58 Nulls and averages
  8. 4:00 Integer division
  9. 4:46 What this does not show
  10. 5:15 Mistakes to avoid
  11. 5:43 My take and next steps
  12. 6:06 Recap

Files

  • mistakes.py (31 lines)
  • run.py (12 lines)
  • shop.py (25 lines)

Download the code (zip)

Tested with

Python 3.12.15

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.

Sources

Learning IT by Example.