>Hi all,
>
>I'm experiencing a pretty odd anomoly using a SQL statement. The syntax for this query is as follows :
>
>SELECT table1.field1,table1.field2,table1.field3,sum(table2.field1)as newfield1, ;
> table2.field2,(sum(table2.field1)+table2.field2) as newfield2,sum(table3.field1) as newfield3 ;
> FROM table1,table2,table3 ;
> WHERE table2.field3 == table1.field1 ;
> AND table3.field2 == table1.field1 ;
> AND table2.field4 == "
" ;
> AND table3.field3 == "" ;
> AND table1.field4 = " ;
> GROUP BY table1.field1 ;
> ORDER BY table1.field1
>
>My problem is that the sum of newfield3 is always twice as much as what is actually in table3.field1. Can anyone explain why this may be happening ?
You probably have only one record in table3 where table3.field2 == table1.field1, but you have two in table2 where table2.field3 == table1.field1, therefore in the resulting (intermediate) cursor it appears twice, and then it gets summed (or more - try to make, say, five records in table2 to match table2.field3 == table1.field1, you'll get five times the sum). There's a workaround for this, though, you'd have to do your sums separately for table1 vs table2, and for table1 vs table3, and then put them together. This means three Selects - two for summings, one for join of the resulting cursors.
