Fix the Incorrect JOIN Logic in Customer Orders Query
Debug and fix a SQL query that attempts to retrieve customer names alongside their total order counts, but currently returns incorrect results due to improper JOIN usage and grouping.
Challenge prompt
You are given a query that tries to list each customer's name along with the total number of orders they have placed. However, the results are inaccurate: some customers appear multiple times, and order counts are incorrect. Identify the issues in the provided SQL query, fix the JOIN logic and grouping, and produce a correct query that returns each customer name exactly once with the correct total order count.
Guidance
- • Check whether the JOIN between customers and orders is appropriate for the desired result.
- • Look carefully at the GROUP BY clause to ensure it's grouping by the correct columns.
Hints
- • INNER JOIN might exclude customers with no orders; consider whether you want to include them.
- • GROUP BY should include all selected non-aggregated columns to avoid duplicate rows.
Starter code
SELECT c.customer_name, COUNT(o.order_id) AS total_orders
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY o.customer_id;Expected output
A result set where each row contains a distinct customer_name and a total_orders count representing how many orders that customer placed. Customers with no orders should appear with a count of 0 if you adjust the JOIN accordingly.
Core concepts
Challenge a Friend
Send this duel to someone else and see if they can solve it.