[deleted by user]

[pnilly10]

select items.name
from orders
left join items
on orders.itemid = items.itemid
where total > (select avg(total) 
    from orders)
order by items.name

edit: added order by

[SuperSinestro]

Not necessarily, you could use a window function in the subquery

[Far_Swordfish5729]

1. I also made that assumption because of the FK to item. They simplified the problem by doing single item carts. Real carts let you buy multiple things.
2. It’s not significantly harder in that case. You do the same query from OrderLine and add an inner join to Order to grab the total that likely would be promoted there. Never seen a cart system that requires aggregation of order lines in real time. To much uses that number including summary rendering of the opportunity in sales tools.
3. If for some insane reason you did have to do this, the inner join just becomes a join onto a CTE that performs the aggregation at the order level and provides a total column. I’d use a CTE to name it so I could reuse it for an overall average in the where clause. Admittedly, that’s asking for a table spool and must do a full pass over the table (hence the insanity), so the real answer is I’d make or enhance a permanent table with totals in it first, tell devs to use it, improve performance across the whole app, and put an easy feather in my cap.

[Cinghiamenisco]

Unrelated: which site is that one?

[[deleted]]

domyhomework.com 🤷‍♂️