Line items per order

Return o.order_id and line_items (the count of matching order_items rows) for every order that has at least one line item. Order by order_id.

JOIN order_items ON oi.order_id = o.order_id, GROUP BY o.order_id, COUNT(*) AS line_items.

Sample input

ordersOrders (order_id shown). Orders with no items are excluded by the INNER JOIN.

order_id
1001
1002
1003
1004

order_itemsItem lines; order_id repeats once per line item (15 rows in the sandbox).

order_id	sku
1001	SKU-A
1001	SKU-B
1001	SKU-C
1002	SKU-C
1002	SKU-D
1004	SKU-A

Expected output

Expected outputOne row per order that has items, with its line-item count. 7 of the 12 orders have items.

order_id	line_items
1001	3
1002	2
1004	3
1005	2
1007	1
1008	2
1010	2

Constraints

JOIN orders to order_items on order_id (an INNER JOIN, so orders with no items are excluded). GROUP BY o.order_id and COUNT(*) the line items, aliased as line_items.

Expected skills

Joining to a finer-grain table and aggregating back to one row per order with GROUP BY.

SQL

AI evaluation

Run the SQL query to inspect preview rows.