Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 3 of 3 total |
Sum from tow tabels |
Tue, Aug 21 2007 5:59 AM | Permanent Link |
SDev | 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 |
Tue, Aug 21 2007 8:33 AM | Permanent Link |
"Robert" | "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 |
Tue, Aug 21 2007 10:00 AM | Permanent Link |
SDev | It is work
Thank you very much |
This web page was last updated on Tuesday, May 7, 2024 at 02:40 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |