SQL
Count Related Items for Each Parent Record
Learn to count associated records from a child table for each parent record using a LEFT JOIN and GROUP BY, useful for summary reports.
SELECT
c.customer_id,
c.customer_name,
COUNT(o.order_id) AS total_orders
FROM
customers AS c
LEFT JOIN
orders AS o ON c.customer_id = o.customer_id
GROUP BY
c.customer_id, c.customer_name
ORDER BY
total_orders DESC;
How it works: This query calculates the total number of orders placed by each customer. It uses a `LEFT JOIN` to ensure that all customers are included in the result, even those who have not placed any orders (in which case `total_orders` would be 0). The `GROUP BY` clause aggregates the counts for each distinct customer, providing a summary of order activity per customer.