Datos · SQL
¿Por qué se duplica el total después de un JOIN en SQL?
Un pedido de 30 €, enviado en 2 paquetes: ¿cuántas filas da el JOIN entre pedidos y paquetes?
¿Y qué devuelve SUM(amount) sobre esas filas?
De un pedido en dos paquetes a un total correcto, en cinco pasos:
¿Por qué sale el mismo importe varias veces después de un JOIN?
Un pedido de 75 € enviado en 3 paquetes sale 3 veces en el JOIN, y cada una de sus 3 filas lleva 75 €.
Tomemos tres pedidos y sus paquetes.
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 € |
¿Qué paquetes llevan el número del pedido 102? Los tocamos en la tabla.
Buscamos 102 en la columna
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
¿Por qué SUM da un total demasiado grande después de un JOIN?
Si el pedido de 75 € sale 3 veces en el JOIN, SUM(amount) lo suma 3 veces: aporta 225 € al total en lugar de 75 €.
Tomemos las mismas tablas, con 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
¿Cómo contar los pedidos sin duplicados después de un JOIN?
Si 12 pedidos enviados dan 20 filas en el JOIN, COUNT(*) devuelve 20 y COUNT(DISTINCT orders.id) devuelve 12.
Tomemos tres pedidos, dos de ellos enviados en varios paquetes.
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
¿Cómo corregir un SUM inflado por un JOIN?
Se reduce la tabla parcels a una fila por pedido antes del JOIN: así, el pedido de 90 € enviado en 4 paquetes entra una sola vez en la suma.
Tomemos tres pedidos, dos de ellos de 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
resultado 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
¿Cómo detectar rápido un total duplicado por un JOIN?
Se va más rápido contando primero los paquetes de cada pedido: es el número de veces que su importe entra en el JOIN con 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