Problem

Given two tables, "orders" and "order_items", write a SQL query to find the total number of items ordered for each order.
orders:
+------------+
| order_id   |
+------------+
| 1          |
| 2          |
| 3          |
+------------+
order_items:
+------------+-------------+
| order_id   | item_count  |
+------------+-------------+
| 1          | 3           |
| 2          | 1           |
| 3          | 2           |
+------------+-------------+
Example output:
+------------+-------------+
| order_id   | total_items |
+------------+-------------+
| 1          | 3           |
| 2          | 1           |
| 3          | 2           |
+------------+-------------+

Solution

SELECT order_id, SUM(item_count) AS total_items
FROM order_items
GROUP BY order_id;

/*

The solution above is optimal because it uses the GROUP BY clause to group the rows by order_id and then uses the SUM function to sum the item_count for each group.

*/