Re: Help creating a view

<sybrandb_at_hccnet.nl> schrieb im Newsbeitrag news:blqo93l8l5lbnu6f7t08e8ee86kcka8pfs_at_4ax.com...
> 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

Thanks a lot

sum(case when beu = 100 then 1 else 0 end), sum(case when beu < 100 then 1 else 0 end)

Regards Peter Received on Tue Jul 17 2007 - 04:21:19 CDT