Help creating a view

From: Peter Keckeis <no_spam_peter.keckeis_at_11er.at>
Date: Tue, 17 Jul 2007 08:20:43 +0200
Message-ID: <469c5fbb$0$3801$5402220f@news.sunrise.ch>

Hi all,
can someone enhance my comprehension of creating a view?

create table bep
  mat char(6),
  beu number(3)
  constraint bep1 primary key mat;

insert into bep values('A',50);
insert into bep values('A',100);
insert into bep values('A',30);
insert into bep values('A',40);
insert into bep values('B',0);
insert into bep values('B',10);
insert into bep values('B',100);
insert into bep values('B',90);
insert into bep values('B',100);

i want to create a view like this
(please interpret this as pseudo code)
create view bep_v

    (li,beua,anz,anza,anzb)
  as select
    mat,
    avg(beu),

    count(mat),
    count(mat) where beu = 100,
    count(mat) where beu < 100

  from bep
  group by (mat);

select * from bep_v;
should bring up the following result:
MAT BEUA ANZ ANZA ANZB
A 55 4 1 3
B 60 5 2 3

Thanks in advance
Peter Received on Tue Jul 17 2007 - 01:20:43 CDT