Hi,

I explain my problem

Main table : Invoice (simplified)
ID  Amount Customer
1    5 000    1
2    4 000    1
3    1 000    1
4    7 500    2
5    2 500    2

Second table : payment
ID Invoice_ID Amount_Paid
1   1               1 500
2   1               1 000
3   1                  500
4   3               1 000
5   4               3 000
6   4               1 500
7   5               1 000    

How replace this query with DBIsam

Select I.Customer, Sum(I.Amount) As Amount, (Select Sum(Amount_paid) From Payment where Invoice_id=I.ID) As Amount_paid

From
 Invoice I

Group By
 I.Customer

the result to obtain

Customer  Amount    Amount_paid
1              10 000      4 000
2              10 000      5 500

"Eric Derrien" <ederrien@hotmail.com> wrote in message
news:F5001B87-BA85-4A8F-9C65-F87F5B9596B0@news.elevatesoft.com...
> Hi,
>
> I explain my problem
>
> Main table : Invoice (simplified)
> ID  Amount Customer
> 1    5 000    1
> 2    4 000    1
> 3    1 000    1
> 4    7 500    2
> 5    2 500    2
>
> Second table : payment
> ID Invoice_ID Amount_Paid
> 1   1               1 500
> 2   1               1 000
> 3   1                  500
> 4   3               1 000
> 5   4               3 000
> 6   4               1 500
> 7   5               1 000
>
> How replace this query with DBIsam
>
> Select I.Customer, Sum(I.Amount) As Amount, (Select Sum(Amount_paid) From
> Payment where Invoice_id=I.ID) As Amount_paid
>
> From
>  Invoice I
>
> Group By
>  I.Customer
>
> the result to obtain
>
> Customer  Amount    Amount_paid
> 1              10 000      4 000
> 2              10 000      5 500
>

There are several ways to do this, but in DBISAM the simplest is probably a
script using memory tables.

SELECT SUM(AMOUNT) INV_AMT, CUSTOMER INV_CUST
INTO MEMORY\TEMP1
FROM INVOICE
GROUP BY 2;
CREATE INDEX BYCUST ON MEMORY\TEMP1 (INV_CUST);
SELECT SUM(AMOUNT_PAID) P_AMT, CUSTOMER P_CUST
INTO MEMORY\TEMP2
FROM PAYMENTS
JOIN INVOICE ON ID = INVOICE_ID
GROUP BY 2;
CREATE INDEX BYCUST ON MEMORY\TEMP2 (P_CUST);
SELECT INV_CUST, INV_AMT, P_AMT
FROM MEMORY\TEMP2
LEFT OUTER JOIN MEMORY\TEMP1 ON INV_CUST = P_CUST;

Note the ; at the end of each select, and the left outer join to get
invoices with no payments.

Robert

Thank you

I did not know that ";" to process a next query
I will test it as soon as possible