General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
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
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only