I have a fairly standard e-commerce schema: one orders row per order, and one order_items row per line item on that order (an order has 1-N items):

sql

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    order_date DATE
);

CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    quantity INTEGER NOT NULL,
    unit_price REAL NOT NULL  -- price at time of order, not a live lookup
);

I need "average order value" for a date range: total revenue divided by number of orders. My first attempt joined the two tables and averaged the line total directly:

sql

SELECT AVG(oi.quantity * oi.unit_price) AS avg_order_value
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
WHERE o.order_date BETWEEN :start AND :end;

This runs without error, but the number is wrong once I check it by hand. Minimal example:

orderitemsorder totalA1 × 100100B3 × 1030

Real average order value: (100 + 30) / 2 orders = 65.

What AVG(quantity * unit_price) actually computes: (100 + 10 + 10 + 10) / 4 rows = 32.50.

The JOIN produces one row per line item, so order B — which is worth less — pulls the average toward its own item prices three times just because it has three items, while order A only counts once. The more items an order has, the more it distorts the average away from being "per order."

I know I could fetch the matching orders and their items into Python and average sum(item.quantity * item.unit_price for item in order.items) per order in application code, but this needs to stay fast as the table grows into the hundreds of thousands of rows, so re-aggregating in Python per request isn't what I want.

What's the correct SQL — and specifically SQLAlchemy Core/ORM — pattern to get a true average-per-order from a normalized orders/order_items table, in one query, without pulling every order's line items into Python first?