Write a SQL query to get the order_id and total number of items for each order.

Question

Given the following 'orders' and 'order_items' tables:
```
orders:
| order_id | customer_id |
|----------|-------------|
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |

order_items:
| order_item_id | order_id | product_id | quantity |
|---------------|----------|------------|----------|
| 1 | 1 | 1 | 2 |
| 2 | 1 | 2 | 1 |
| 3 | 2 | 1 | 1 |
```
Which SQL query retrieves the order_id and total number of items for each order?

Accepted Answer

SELECT order_id, SUM(quantity) FROM order_items GROUP BY order_id;

Answer

SELECT order_id, COUNT(*) FROM order_items GROUP BY order_id;

Answer

SELECT order_id, COUNT(order_item_id) FROM order_items GROUP BY order_id;

Answer

SELECT order_id, COUNT(product_id) FROM order_items GROUP BY order_id;

Question from the SQL - Fundamentals test

Write a SQL query to get the order_id and total number of items for each order.