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!