Daten · SQL
Warum verdoppelt ein SQL-Join die Summe?
Eine Bestellung über 30 €, verschickt in 2 Paketen: Wie viele Zeilen liefert der Join von Bestellungen und Paketen?
Und was liefert SUM(amount) für diese Zeilen?
Von einer Bestellung in zwei Paketen bis zur richtigen Summe, in fünf Etappen:
Warum steht nach einem Join derselbe Betrag mehrmals da?
Eine Bestellung über 75 €, verschickt in 3 Paketen, steht 3-mal im Join, und jede ihrer 3 Zeilen trägt 75 €.
Nehmen wir drei Bestellungen und ihre Pakete.
SELECT orders.id, amount FROM orders JOIN parcels ON parcels.order_id = orders.id
| id | amount |
|---|---|
| 101 | 40 € |
| 102 | 25 € |
| 103 | 60 € |
| id | order_id |
|---|---|
| 501 | 101 |
| 502 | 102 |
| 503 | 102 |
| 504 | 103 |
| orders.id | amount |
|---|---|
| 101 | 40 € |
| 102 | 25 € |
| 102 | 25 € |
| 103 | 60 € |
Welche Pakete tragen die Nummer der Bestellung 102? Tippe sie in der Tabelle an.
Du suchst die 102 in der Spalte
order_id.
SELECT orders.id, amount FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 35 € 102 80 € 103 15 € 104 50 € parcels id order_id 501 101 502 102 503 102 504 102 505 103 506 104 SELECT orders.id, amount FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 20 € 102 70 € 103 45 € 104 60 € parcels id order_id 501 104 502 102 503 101 504 101 505 104 506 104
Warum liefert SUM nach einem Join eine zu große Summe?
Steht die Bestellung über 75 € 3-mal im Join, addiert SUM(amount) sie 3-mal: Sie zählt in der Summe 225 € statt 75 €.
Nehmen wir dieselben Tabellen, mit SUM.
SELECT SUM(amount) FROM orders JOIN parcels ON parcels.order_id = orders.id
| id | amount |
|---|---|
| 101 | 40 € |
| 102 | 25 € |
| 103 | 60 € |
| id | order_id |
|---|---|
| 501 | 101 |
| 502 | 102 |
| 503 | 102 |
| 504 | 103 |
| orders.id | amount |
|---|---|
| 101 | 40 € |
| 102 | 25 € |
| 102 | 25 € |
| 103 | 60 € |
| SUM(amount) |
|---|
| 150 € |
SELECT SUM(amount) FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 50 € 102 15 € 103 70 € parcels id order_id 501 101 502 102 503 102 504 102 505 103 SELECT SUM(amount) FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 35 € 102 80 € 103 20 € 104 45 € parcels id order_id 501 101 502 101 503 102 504 104
Wie zählt man Bestellungen nach einem Join ohne Duplikate?
Ergeben 12 verschickte Bestellungen 20 Zeilen im Join, liefert COUNT(*) 20 und COUNT(DISTINCT orders.id) 12.
Nehmen wir drei Bestellungen, zwei davon in mehreren Paketen.
SELECT COUNT(*),
COUNT(DISTINCT orders.id)
FROM orders
JOIN parcels
ON parcels.order_id = orders.id| id | amount |
|---|---|
| 101 | 40 € |
| 102 | 25 € |
| 103 | 60 € |
| id | order_id |
|---|---|
| 501 | 101 |
| 502 | 101 |
| 503 | 102 |
| 504 | 103 |
| 505 | 103 |
| 506 | 103 |
| orders.id | amount |
|---|---|
| 101 | 40 € |
| 101 | 40 € |
| 102 | 25 € |
| 103 | 60 € |
| 103 | 60 € |
| 103 | 60 € |
| COUNT(*) | COUNT(DISTINCT orders.id) |
|---|---|
| 6 | 3 |
SELECT COUNT(*) FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 15 € 102 50 € 103 35 € 104 70 € parcels id order_id 501 101 502 102 503 102 504 102 505 103 506 104 507 104 SELECT COUNT(DISTINCT orders.id) FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 80 € 102 20 € 103 45 € 104 60 € parcels id order_id 501 101 502 103 503 101 504 104 505 104
Wie korrigiert man ein zu großes SUM nach einem Join?
Du verkleinerst die Tabelle parcels vor dem Join auf eine Zeile pro Bestellung: Die Bestellung über 90 € in 4 Paketen kommt dann nur einmal in die Summe.
Nehmen wir drei Bestellungen, zwei davon über 40 €.
SELECT SUM(amount)
FROM orders
JOIN (SELECT DISTINCT order_id
FROM parcels) AS shipped
ON shipped.order_id = orders.id| id | amount |
|---|---|
| 101 | 40 € |
| 102 | 25 € |
| 103 | 40 € |
| id | order_id |
|---|---|
| 501 | 101 |
| 502 | 101 |
| 503 | 102 |
| 504 | 103 |
| order_id |
|---|
| 101 |
| 102 |
| 103 |
| SUM(amount) |
|---|
| 105 € |
SELECT SUM(DISTINCT amount) FROM orders JOIN parcels ON parcels.order_id = orders.id
Ergebnis SUM(DISTINCT amount) 65 €
SELECT SUM(amount) FROM orders JOIN (SELECT DISTINCT order_id FROM parcels) AS shipped ON shipped.order_id = orders.idorders id amount 101 35 € 102 50 € 103 20 € 104 70 € parcels id order_id 501 101 502 101 503 103 504 104 505 104 506 104 SELECT SUM(DISTINCT amount) FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 45 € 102 15 € 103 45 € 104 80 € parcels id order_id 501 103 502 102 503 102 504 101 505 103 506 104
Wie erkennt man schnell eine vom Join verdoppelte Summe?
Schneller geht es, wenn du zuerst die Pakete jeder Bestellung zählst: So oft steht ihr Betrag im Join mit parcels.
SELECT SUM(amount) FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 25 € 102 60 € 103 15 € 104 80 € parcels id order_id 501 101 502 102 503 102 504 103 SELECT COUNT(DISTINCT orders.id) FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 20 € 102 45 € 103 35 € 104 50 € parcels id order_id 501 104 502 102 503 101 504 101 505 101 SELECT SUM(DISTINCT amount) FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 70 € 102 25 € 103 70 € 104 15 € parcels id order_id 501 103 502 102 503 101 504 101 SELECT SUM(amount) FROM orders JOIN (SELECT DISTINCT order_id FROM parcels) AS shipped ON shipped.order_id = orders.idorders id amount 101 40 € 102 80 € 103 50 € 104 35 € parcels id order_id 501 101 502 102 503 102 504 102 505 104 506 104