Re: sql question

Guy

You need some correlation between the orderfile and orderdetail - something like

where orderdetail.orderno = orderfile.orderno

in the subquery. Your version will give the same sum for everything, I believe. ;-)

Rob mentions a left outer join, which would give results when there is no detail - that may or may not be desired. Your version, with the correlation, might work like a left outer join - not sure - or might return a NULL in that case - that'd be a great time to use the COALESCE function, to give it a 0 and not a NULL.

HTH
Vern

-------------- Original message ----------------------
From: GUY_HENZA@xxxxxxxxxxxxxx

Yes.

Select orderno, orderamt, (Select sum(orderamt) from orderdetail) from
orderfile

Regards,

Guy

"tim"
<tim2006@xxxxxxxxxxx To: "'Midrange Forumn'"
<midrange-l@xxxxxxxxxxxx>
> cc:
Sent by: Subject: sql question
midrange-l-bounces@m
idrange.com

12/10/2007 03:03 PM
Please respond to
Midrange Systems
Technical Discussion

I wish to do this in 1 sql statement not not sure if I can.

Select orderno, orderamt from orderfile

Select sum(orderamt) from orderdetail

Resulting in:

Order no, order amount, sum of order amounts from detail file.

Can this be done in 1 sql?