sql server - How to compare row with subquery output in SQL? -

there 2 tables, orderid , order details.

orders:

orderid pk freight |....| 

order details:

orderid pk fk productid pk fk unitprice quantity |....| 

each orderid unique orders, order details can contain several details same orderid , different productid, unitprice etc.

so, in order details can see two, 3 or more orders (orderid).

my task select physical addresses of freight records more total cost of entire order freight > unitprice * quantity * (quantity of orderid in order details)

select %%physloc%% orders freight > (select sum(unitprice * quantity)                  [order details]                  group orderid); 

and of course i've got

'subquery returned more 1 value...'

i try use top in case have wrong selection.

all need compare somehow each freight orderid records each records subquery same orderid. have no idea how.

maybe can find different way, great.

i use sql server 2008

thank all.

my task select physical addresses of freight records more total cost of entire order freight > unitprice * quantity * (quantity of orderid in order details)

you can achieve using aliases , filtering subquery in following way...

select %%physloc%% orders o o.freight > (select sum(od.unitprice * od.quantity)                  [order details] od                  od.orderid = o.orderid); 

you don't need group by clause here @ because potentially split/group results. using sum aggregate function you're returning scalar value can further filtered where clause