I have 3 tabels
1-Customers
  custNo CustName ..

2-Invoices
  custNo invNo amount ...

2-Payments
  custNo payNo amount ....

what i want is to make qyary to select ecah custome with sum of amount in invoices and sum of amount in payment in one record like this:

custNo sumOfInvAmount sumOfPayAmount

also if theres no invoices for the customer the sumOfinvAmount become 0, same thing for payment amount,
and if there no invoices nor payments i get record with 0 valus in the sumOfinvAmount anf sumOfPayAmount.

Samples of 4 cases


---------       ----------------     -----------------
Cust No        Total Invices     Total Paymens
---------       ----------------     -----------------
  1                   15000                 5800
  2                   12500                    0
  3                       0                     4500
  4                       0                        0
---------------------------------------------------

Thank you

"SDev" <SDev@somewereinthworld.com> wrote in message
news:FE2FA8C9-2AD3-49E3-8ECE-86BECD827F59@news.elevatesoft.com...
>I have 3 tabels
> 1-Customers
>   custNo CustName ..
>
> 2-Invoices
>   custNo invNo amount ...
>
> 2-Payments
>   custNo payNo amount ....
>
> what i want is to make qyary to select ecah custome with sum of amount in
> invoices and sum of amount in payment in one record like this:
>
> custNo sumOfInvAmount sumOfPayAmount

Probably the fastest and most efficient way is a script, as follows (note
that you will need an OUTER JOIN to get the customers with no invoices
and/or payments)

SELECT CUSTNO PCUSTNO, SUM(AMOUNT) PAYMENTS
INTO MEMORY\TEMP1
FROM PAYMENTS
GROUP BY 1;
CREATE INDEX BYCUST ON MEMORY\TEMP1 (PCUSTNO);
SELECT CUSTNO ICUSTNO, SUM(AMOUNT) INVOICES
INTO MEMORY\TEMP2
FROM INVOICES
GROUP BY 1;
CREATE INDEX BYCUST ON MEMORY\TEMP2 (ICUSTNO);
SELECT CUSTNO, CUSTNAME, INVOICES, PAYMENTS
FROM CUSTOMERS
LEFT OUTER JOIN MEMORY\TEMP1 ON PCUSTNO = CUSTNO
LEFT OUTER JOIN MEMORY\TEMP2 ON ICUSTNO = CUSTNO;

Robert

It is work

Thank you very much