Board index » delphi » Local Sql problem w/ Aggregates

Local Sql problem w/ Aggregates

The following is standard SQL, works w/ interbase, but not local SQL --
I really need this!  Any ideas for work-arounds?

The simplified example:

Invoice
------------------------------------
inv_num      ....

Invoice_Line
------------------------------------
inv_num    item      Price    ...

and then to find the total of each invoice

select i.invoice_number,
(select sum(l.Price) from invoice_line l where l.inv_num = i.inv_num) as
INV_TOTAL
from invoice i

I need this to work in real-time as SQL, thus, can't keep the total in
the invoice record...

Any ideas?

ADVthanksANCE,

-dl

  vcard.vcf
< 1K Download
 

Re:Local Sql problem w/ Aggregates


Quote
>select i.invoice_number,
>(select sum(l.Price) from invoice_line l where l.inv_num = i.inv_num) as
>INV_TOTAL
>from invoice i

try

select D1.invoice_number,  sum(D2.Price) INV_TOTAL
from invoice_line D2,  invoice D1  where D1.inv_num = D2.inv_num
Groupby D1.invoice_number

--
Brian Bushay (TeamB)
Bbus...@DataGuidance.com

Other Threads