SQL
Counting Related Records with SQL JOIN
Discover how to count the number of related items for each record in a main table using a LEFT JOIN and GROUP BY clause in SQL.
SELECT c.customer_id, c.customer_name, COUNT(o.order_id) AS total_orders
FROM customers c
LEFT JOIN orders 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 SQL query retrieves each customer's ID and name, along with the total number of orders they have placed. It uses a `LEFT JOIN` to include all customers, even those with no orders, and `COUNT(o.order_id)` with `GROUP BY` to aggregate the order count for each unique customer. The `ORDER BY` clause sorts the results by the number of orders in descending order.