Hi Jess,

Even with the 'order by' the ansewrs are not what one would expect.

Ken


>You forgot to include ORDER BY t1.id before GROUP
>
>TIA
>
>>i have 3 tables:
>>create table t1 (id I))
>>create table t2 (id I,fid i,amount n(10,2)
>>create table t3 (id I,fid i,amount n(10,2)
>>
>>the following sql returns the wrong sum:
>>select dist t1.*,sum(t2.amount) as n2,sum(t3.amount) as n3 ;
>>from t1 ;
>>inner join t2 on t1.id=t2.fid;
>>inner join t3 on t1.id=t3.fid ;
>>group by t1.id
>>
>>data:
>>
>>t1.id=1
>>t2.id=100,t2.fid=1,t2.amount=50
>>t2.id=101,t2.fid=1,t2.amount=60
>>t2.id=102,t2.fid=1,t2.amount=40
>>
>>t3.id=98,t3.fid=1,t3.amount=10
>>t3.id=99,t3.fid=1,t3.amount=15
>>
>>we expect the n2 to be 150 and n3 to be 25
>>
>>but we get n2=300, n3=75
>>notice each sum is the real sum* the count of the other record.
>>what am I doing wrong??
>>
>>
>>Thanks in advance, see ya in NAWLINS
>>
>>Peter