summing from different tables

From: mike <hillmw_at_charter.net>
Date: 27 Jan 2006 10:01:55 -0800
Message-ID: <1138384915.643399.30500@g44g2000cwa.googlegroups.com>

Suppose I had 4 tables like

tbl_a:
num

tbl_b
type_id
type_desc

tbl_c
num
type_id
act

tbl_d
num
type_id
est

If I sum the amounts from tbl_c, this works

select num, decode(type_id,1,'Cost',2,'Savings') , sum(act) from tbl_c
group by num, type_id

If I sum the amounts from tbl_d, this works as well

select num, decode(type_id,1,'Cost',2,'Savings') , sum(est) from tbl_d
group by num, type_id

What I would like to do is get 1 view of both the estimates and actuals, but I am not getting accurate results:

Here is my sql:

select a.num, decode(b.type_id,1,'Cost',2,'Savings') ,sum(c.act), sum(d.est)
from tbl_a a, tbl_b b, tbl_c c, tbl_d d
where a.num=c.num and

a.num=d.num and
b.type_id=c.type_id and
b.type_id=d.type_id

group by a.num, b.type_id

In some cases some of the rows in d are not here and the sums are different as well.

Any help is appreciated.

MNIke Received on Fri Jan 27 2006 - 12:01:55 CST