A covering index for unit totals

The report “units sold per item” reads order_lines.item_id and quantity. Create an index named idx_lines_item_qty on order_lines that starts with item_id and also contains quantity, so SQLite can answer from the index alone. Then return item_id and units (sum of quantity) per item.

what to return

Columns: item_idunits in this order
Rows: 12 rows · any order
Watch for: Column order in the index matters: item_id first, then quantity.
Skill: Design a composite index that covers a query. Refresher: SARGable ranges, not speed commandments →

expected result

Your query should return exactly these columns and values. Run it and compare.

expected result12 rows · any order

item_id	units
1	6
2	4
3	9

first 3 of 12 rows

data you can query

Sample rows from each table. Each column shows its type underneath; → marks a link to another table.

order_lines4 columns · 48 rows

order_idinteger → sales_orders	item_idinteger → catalog_items	quantityinteger	unit_pricereal
101	1	1	39
101	3	2	19.5
102	4	1	59
103	5	1	45
103	10	3	12

showing 5 of 48 rows

solution.sql

Hints and the answer are always available. Using them counts the question as solved with help.