Re: Order output by sum from two other tables

[Parijat Sahai <parijatsahai@HOTMAIL.COM>]

Hi,

Why don't you simply join the three tables, GROUP BY item, and select
the sum? E.g.,

begin-select
T1.item
sum(T2.profit)+sum(T3.profit) &Total_Sum
sum(T2.profit) &T2_Sum
sum(T3.profit) &T3_Sum

from T1, T2, T3
where T1.item = T2.item
 and T2.item = T3.item
Group By T1.item
Order By 2
end-select

I guess this should give you what you want.

Parijat.

>
>sqr3/3.0.15/PC/Windows95/Oracle 7.2.2.3/Aug 24 1998 (??)
>
>Hello!!
>I=B4m having trouble creating a "top-list" ordered by a totalsum from
>different tables.
>This may be a simple problem, I really hope so, but I have=B4nt found =
>an
>good answer
>in spite of all efforts.
>Now the temporary solution is loading a new temporary table with the
>items and the result from a calculation (giving the total sum). Then
>again selecting the items and totalsum from the new table,this time
>ordered by totalsum.(This way I have to do the calculation-procedure
>twice.
>I would like a result something like this:
>
>******  Toplist
>T1.item     Total (sum(T2.profit) + sum(T3.profit))     sum(T2.profit)
>sum(T3.profit) =20
>3                   7               2       5
>1                   4               4       0
>2                   3               1       2
>****
>
>Anyone having an idea of a more simple way of doing this????=20
>
>Thanks in advance
>
>//Karin
>=09
>This is an example of my code now:
>
>begin-program
>        do main
>        do main2
>end-program
>        *******
>
>begin-procedure main
>        begin-select
>T1.item         &item
>        let $item =3D &item
>        do calculation
>        do load_tbl
>        from T1
>        end-select
>end-procedure main
>        *******
>
>begin-procedure main2
>        begin-select
>t.item          &titem
>t.total         &total
>        do calculation  !to be able to print the profit for table 2 and
>3 each
>        print &total (+1,10)
>        print &titem (,1)
>        print #T2profit (,20)
>        print #T3profit (,30)
>        group by t.item, t.total
>        order by t.total
>        end-select
>end-procedure main2
>                *********
>
>begin-procedure calculation
>        begin-select
>sum(T2.profit)          &T2profit
>sum(T3.profit)          &T3profit
>        let #T2profit =3D &T2profit
>        let #T3profit =3D &T3profit
>        let #Total =3D #T2profit + #T3profit
>        =20
>        from T2, T3
>        where T2.item =3D $item
>        and T3.item =3D $item   =09
>        end-select
>end-procedure calculation
>        *******
>
>begin-procedure load_tbl
>        begin-sql
>        insert into temp_tbl=20
>        values($item, #Total);
>        commit;
>        end-sql
>end-procedure load_tbl
>
>
>
>
>
>> ---------------------------------
>> Karin =D6berg
>> Direct   +46 (0)19 - 32 42 31
>> Mobile  +46 (0)70 - 332 42 31
>> karin.oberg@meteorit.se
>>         http://www.meteorit.se   =20
>>=20


______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com