Re: SQL sum question

From: Jayadas C <jayadasc_at_emirates.com>
Date: 1997/07/26
Message-ID: <01bc9971$f7e0f2c0$507daac2@eucit>#1/1

hi rob !

try this query ... it should do the job !

create view sum2v as
select kode,

       sum(decode(jaar,96,bedrag,null)) sumbedrag96,
       sum(decode(jaar,97,bedrag,null)) sumbedrag97

from sum2
group by kode;

Rob Mocking <rmocking_at_euronet.nl> wrote in article <5rapkb$640_at_news.euro.net>...
> Greetings,
>
> I'm trying to construct a view wich contains two columns based on
> different sum functions of the same base table. In the following SQL
> script view sum2v produces the required results, but I have a feeling
> I'm doing things the hard way.
>
> create table sum2 (
> kode number,
> jaar number,
> bedrag number
> );
> insert into sum2 values(1, 96, 1);
> insert into sum2 values(1, 96, 2);
> insert into sum2 values(1, 96, 3);
> insert into sum2 values(2, 96, 4);
> insert into sum2 values(2, 96, 5);
> insert into sum2 values(2, 96, 6);
> insert into sum2 values(1, 97, 7);
> insert into sum2 values(1, 97, 8);
> insert into sum2 values(1, 97, 9);
> insert into sum2 values(2, 97, 10);
> insert into sum2 values(2, 97, 11);
> insert into sum2 values(2, 97, 12);
> commit;
> select * from sum2;
> create view sum2v96 as
> select kode, jaar, sum(bedrag) sumbedrag
> from sum2
> where jaar = 96
> group by kode, jaar;
> select * from sum2v96;
> create view sum2v97 as
> select kode, jaar, sum(bedrag) sumbedrag
> from sum2
> where jaar = 97
> group by kode, jaar;
> select * from sum2v97;
> create view sum2v as
> select a.kode, a.sumbedrag sumbedrag96, b.sumbedrag sumbedrag97
> from sum2v96 a, sum2v97 b
> where a.kode = b.kode;
> select * from sum2v;
>
> Please share your valuable SQL knowledge with me (and all other
> newsgroup readers) and come up with a more efficient/ellegant
> solution.
>
> Thanks in advance,
> Rob Mocking
>
>
>
Received on Sat Jul 26 1997 - 00:00:00 CDT