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.