Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> SQL sum question

SQL sum question

From: Rob Mocking <rmocking_at_euronet.nl>
Date: 1997/07/25
Message-ID: <5rapkb$640@news.euro.net>#1/1

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 Fri Jul 25 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US