Becuase I hate to see in my code:
NVL(Field,0000000.00) AS ...
I use new VFP9 function CAST. No the answer is in how join these tables. I used left join, you only JOIN
That way I get in the result cursor (w/o gouping)

ID   Tbl2.PK  Tbl2.Sumv  Tbl3.PK    Tbl3.Sumv
1       1       200        2           200
1       NULL    NULL       3           400

With only JOIN you get

ID   Tbl2.PK  Tbl2.Sumv  Tbl3.PK    Tbl3.Sumv
1       1       200        2           200

(at least I get that)

>>>I have the following SQL that sometimes gives me erroneous results:
>>>
>>>

>>>select GROUP_FLD, NVL(SUM(MyTable2.Hours),00000.00) as TotHrs,
>>>NVL(SUM(MyTable3.Cases),00000.00) as TotCases from MyTable1
>>>join MyTable2 on MyTable2.pk_fld = MyTable1.pk_fld
>>>join MyTable3 on MyTable3.pk_fld = MyTable1.pk_fld
>>>group by GROUP_FLD
>>>

>>>
>>>What happens is that if Table3 has 2 records and Table2 has 1
>>>records, total of 2 two records are selected before VFP starts to group and SUM() the records. And the SUM(MyTable2.Hours) shows duplicate number of hours (as it calculates based on two records). Whereas in fact it should be on 1 record.
>>>
>>>Is the only solution to the above problem to move the SUM(...) to SUBQUERIES?
>>>
>>>Thank you.
>>
>>

>>CREATE TABLE Main (Id I, Tabl2FK I, Tabl3FK I)
>>INSERT INTO Main VALUES (1,1,2)
>>INSERT INTO Main (Id, Tabl3FK) VALUES (1,3)
>>
>>CREATE TABLE Tbl2 (PK I, SumV N(10,2))
>>INSERT INTO Tbl2 VALUES (1,200)
>>
>>CREATE TABLE Tbl3 (PK I, SumV N(10,2))
>>INSERT INTO Tbl3 VALUES (2,200)
>>INSERT INTO Tbl3 VALUES (3,400)
>>
>>SELECT Main.Id, SUM(NVL(Tbl2.SumV,cast(0 as N(10,2))) AS Sum2,;
>>                SUM(NVL(Tbl3.SumV,cast(0 as N(10,2))) AS Sum3;
>>FROM Main;
>>LEFT JOIN Tbl2 ON Main.Tabl2FK = Tbl2.PK;
>>LEFT JOIN Tbl3 ON Main.Tabl3FK = Tbl3.PK;
>>GROUP BY Main.Id;
>>INTO CURSOR test
>>BROWSE NORMAL
>>

>
>Borislav,
>
>I ran your example as is, and it works (except you missed a couple of parentheses). I have never used the function "cast()", don't know what it does. I will have to read on it. Is this what made your example work, the use of Cast()?
>
>Thank you very much.