sql server - Use a calculated column in a where clause -

i'm trying use calculated column in clause.

i've trying cross apply, sub-query select not give me near need.

my query far:

select p.code, c.accountnumber, sales = (sum(p.unitprice) * sum(od.qtyshipped)) [dbo].customer c      left join [dbo].orderheader oh on oh.customerid = c.id      left join [dbo].orderdetail od on od.orderheaderid = oh.id     left join [dbo].product p on p.id = od.productid sales > 100 group p.code, c.accountnumber, sales 

this not work, 'sales' invalid column

you'll need wrap inner query in derived table or cte in order able use derived columns in where clause (also, note sum() specified once, after multiplication):

select x.code, x.accountnumber, x.sales (   select p.code, c.accountnumber, sum(p.unitprice *od.qtyshipped) sales    [dbo].customer c        left join [dbo].orderheader oh on oh.customerid = c.id        left join [dbo].orderdetail od on od.orderheaderid = oh.id       left join [dbo].product p on p.id = od.productid   group p.code, c.accountnumber ) x x.sales > 100;