Thank you!
Now just a few questions about your solution:

Why do you query II.ItemID?
What does GROUP BY 1, 2, 3, 4 mean in the query?

So as I understand if I have the following data:

InvoiceID CustomerName CustomerAddress
1 John Smith High Street 2.

InvoiceID ItemID Quantity ItemNetPrice VATPercentage
1 1 1 $10 5
1 2 1 $15 20
1 4 1 $20 5

The query should return:
Invoice ID, CustomerName, CustomerAddress, Sum1, Sum2, Sum3
1 John Smith High Street 2. $45 $4,5 $49,5

Is that right?

Thanks again!

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@...> wrote:

>
> SELECT I.InvoiceID, I.CustomerName, I.CustomerAddress, II.ItemID,

Sum(II.ItemNetPrice * II.Quantity),

> Sum(II.ItemNetPrice * II.Quantity * II.VATPercentage / 100),
> Sum(II.ItemNetPrice * II.Quantity * (1 + II.VATPercentage /

100))

> FROM Invoices I
> JOIN InvoiceItems II ON I.InvoiceID = II.InvoiceID
> GROUP BY 1, 2, 3, 4
>
> or something similar.
>
> HTH,
> Set
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com [mailto:firebird-

support@yahoogroups.com] On Behalf Of zzotnik

> Sent: 27. mars 2008 13:06
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Complicated Query - how?
>
> Dear Group,
>
> I need your help please, to optimize a query in FireBird.
>
> I'm writing an Invoicing program. The Invoice Metadata is stored in
> one table, and multiple items for each invoice are stored in a
> separate table, like:
>
> TABLE Invoices -> InvoiceID, CustomerName, CustomerAddress, etc.
> TABLE InvoiceItems -> ItemID, InvoiceID, ItemName, ItemNetPrice,
> VATPercentage and so on for each invoice item.
>
> I would like to create a stored procedure which returns all of the
> invoices, and calculates the invoice sum, vat sum, and total sum

for

> each invoice.
>
> So I have the following queries:
>
> Query1:
> FOR SELECT InvoiceID, CustomerName, CustomerAddress
> FROM Invoices INTO :InvoiceID, :CustomerName, :CustomerAddress
> DO SUSPEND;
>
> Query2:
> SELECT Sum(ItemNetPrice * Quantity), Sum(ItemNetPrice * Quantity *
> VATPercentage / 100), Sum(ItemNetPrice * Quantity * (1 +
> VATPercentage / 100))
> FROM InvoiceItems WHERE InvoiceID = :InvoiceID GROUP BY ItemID;
>
> How could I combine these querys into one single query, so I won't
> have to execute Query2 for each line of Query1?
>
> Thank you!
>