Re: summing from different tables

mike wrote: 
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 

And that is why there is this thing called an OUTER JOIN.

Click on Morgan's Library at www.psoug.org
Click on Joins
Run the inner and outer join demos (ISO syntax)
--
Daniel A. Morgan
http://www.psoug.org
damorgan@xxxxxxxxxxxxxxxx
(replace x with u to respond)
.

Relevant Pages

  • Re: New Id usign max(id)
    ... > mike wrote: ... >> NUM INTEGER NOT NULL, ... > could populate it via a sequence. ...
    (comp.databases.oracle.misc)
  • Re: SumProduct Function
    ... Don Guillett ... Microsoft MVP Excel ... "Mike H." ... sometimes why it would return #NUM instead. ...
    (microsoft.public.excel.programming)
  • summing from different tables
    ... If I sum the amounts from tbl_d, ...
    (comp.databases.oracle.misc)