>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