Re: Help creating a view

On Tue, 17 Jul 2007 08:20:43 +0200, "Peter Keckeis" <no_spam_peter.keckeis_at_11er.at> wrote:

>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
>

Hint :
use sum and case
sum (case /*pseudo*/ add 1 when true, 0 when false ) ditto for the second expression.

-- 
Sybrand Bakker
Senior Oracle DBA