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_at_x.washington.edu
(replace x with u to respond)