On 2012-06-28 15:02, Tapomay Dey wrote:

I performed the following operations sequentially. Please look at the result when I use SUM. Sum values are incorrect. They should be 60/600/6000/60000.AVG returns correct values though. What could be going wrong? Is it something I can fix?

sql> sql>create table table1 (id int, value int); operation successful (64.427ms) sql>create table table2 (id int, value int); operation successful (78.679ms) sql>insert into table1 values(1, 10); 1 affected row (25.389ms) sql>insert into table1 values(1, 20); 1 affected row (28.254ms) sql>insert into table1 values(1, 30); 1 affected row (27.512ms) sql>insert into table1 values(2, 300); 1 affected row (28.255ms) sql>insert into table1 values(2, 200); 1 affected row (28.658ms) sql>insert into table1 values(2, 100); 1 affected row (29.606ms) sql>insert into table2 values(1, 1000); 1 affected row (25.144ms) sql>insert into table2 values(1, 2000); 1 affected row (26.130ms) sql>insert into table2 values(1, 3000); 1 affected row (27.490ms) sql>insert into table2 values(2, 30000); 1 affected row (18.623ms) sql>insert into table2 values(2, 20000); 1 affected row (40.441ms) sql>insert into table2 values(2, 20000); 1 affected row (29.716ms) sql>insert into table2 values(2, 10000); 1 affected row (25.907ms) sql>select t1.id, sum(t1.value) as v1, sum(t2.value) as v2 from table1 t1, table2 t2 where t1.id=t2.id group by t1.id; +------+------+--------+ | id | v1 | v2 | +======+======+========+ | 1 | 180 | 18000 | | 2 | 2400 | 240000 | +------+------+--------+ 2 tuples (1.769ms) sql>select t1.id, avg(t1.value) as v1, avg(t2.value) as v2 from table1 t1, table2 t2 where t1.id=t2.id group by t1.id; +------+--------------------------+--------------------------+ | id | v1 | v2 | +======+==========================+==========================+ | 1 | 20 | 2000 | | 2 | 200 | 20000 | +------+--------------------------+--------------------------+ 2 tuples (2.275ms) sql>

Regards, Tapomay.

I think this will explain it:

sql>select t1.id, t1.value, t2.value from table1 t1, table2 t2 where t1.id=t2.id; +------+-------+-------+ | id | value | value | +======+=======+=======+ | 1 | 10 | 1000 | | 1 | 10 | 2000 | | 1 | 10 | 3000 | | 1 | 20 | 1000 | | 1 | 20 | 2000 | | 1 | 20 | 3000 | | 1 | 30 | 1000 | | 1 | 30 | 2000 | | 1 | 30 | 3000 | | 2 | 300 | 30000 | | 2 | 300 | 20000 | | 2 | 300 | 20000 | | 2 | 300 | 10000 | | 2 | 200 | 30000 | | 2 | 200 | 20000 | | 2 | 200 | 20000 | | 2 | 200 | 10000 | | 2 | 100 | 30000 | | 2 | 100 | 20000 | | 2 | 100 | 20000 | | 2 | 100 | 10000 | +------+-------+-------+ 21 tuples (2.758ms)

The sum calls count the values multiple times since each id in one table matches multiple rows in the other.