Remove Orders Saved Twice
EasyDescription
The checkout saved some orders twice. First run this statement, which saves orders 33 and 57 again:
CodeINSERT INTO orders (customer_id, order_date, status, total_amount, shipping_city) SELECT customer_id, order_date, status, total_amount, shipping_city FROM orders WHERE order_id IN (33, 57);
An order is a copy when an order with a lower order_id has the same customer_id,
order_date, total_amount and shipping_city. Delete every copy, so that the first order of
each group stays.
Then finish with exactly this query:
CodeSELECT o.customer_id, COUNT(DISTINCT o.order_id) AS orders, COUNT(oi.item_id) AS lines FROM orders o LEFT JOIN order_items oi ON oi.order_id = o.order_id WHERE o.customer_id IN (7, 11, 25) GROUP BY o.customer_id ORDER BY o.customer_id;
Tables
| orders |
|---|
| order_id (PK) |
| customer_id (FK) |
| order_date |
| status |
| total_amount |
| shipping_city |
| order_items |
|---|
| item_id (PK) |
| order_id (FK) |
| product_id (FK) |
| quantity |