>>Hello,
>>
>>I try to do an SQL, summarizing invoices, and summarizing the payments to the invoices. In this example, the payment uses Subscriber ID (naSubId) and sequence (naSequence) and the invoice date (naInvDate) to join.

>>SELECT	.F. AS naSelect, ;
>>		CN.naSubId, ;
>>		CN.naSequence, ;
>>		CN.naFullName, ;
>>		CM.naInvDate, ;
>>		COUNT(CM.naSubId) AS FXCnt, ;
>>		SUM(CM.NaInvAmt) AS FXSum, ;
>>		SUM(CH.naPayAmnt) AS FXPaid, ;
>>		0000000000.00 AS Balance ;
>>	FROM cuName CN ;
>>		LEFT OUTER JOIN cuMaster CM ;
>>		ON CM.naSubId = CN.naSubId ;
>>		AND CM.NaSequence = CN.naSequence ;
>>		LEFT OUTER JOIN cuHistory CH ;
>>		ON CH.naSubId = CN.naSubId ;
>>		AND CH.naSequence = CN.naSequence ;
>>	GROUP BY CN.naSubId, CN.naSequence ;
>>	INTO CURSOR cuImport READWRITE
>>

>>It happens with this code, that the invoice sum (FXSum) is multiplied by the number of payments for that invoice. I do not know what is the right syntax to get the correct Total of Invoices and the total of payments.
>
>Christian,
>IMHO this type of things are not suitable for just one SQL. You're also using nonaggregate fields in your select which would be right only if your data is designed to be that way. To your chance it's only FXSum that gets multiplied by the number of payments. If data wasn't a special one (sounds it's) also FxCount and FxPaid would be multiplied. Using distinct (ie: sum(distinct NaInvAmt)) would make it more realistic but still not correct and could be used once in an SQL.
>
>Sample for potential error :
>CuMaster has 3 (with naInvAmt 100,200,300) and CuHistory has 6 entries (with naPaid 10,20,30,40,50,60) for the same naSubId, naSequence (sounds your data never has though).
>You'd get 18 as cnt, (100+200+300)*6 as InvSum and (10,20,30,40,50,60)*3 as PaidSum.
>
>I'd get cnt() and sum() in separate cursors first and then consolidate to one.
>Cetin

Yes, I do now two separate cursors and it works. I was thinking it must be possible to do this in one cursor and I'm doing something wrong.
Thanks.