"Svein Erling Tysvfr" <svein.erling.tysvaer@...> wrote in
message news:3.0.1.32.20020226123010.00a46390@[158.36.132.22]...

>
> I don't think there is anything you can do with a view that you cannot do
> without it.

Ok, Svein, I bite, please put this in a straight query.
:-)

create view v(f, f2, c) as
select f, f2, count(f3)
from tbl
group by f, f2;

select f, avg(c)
from v
group by f;

There are less tricky but real examples that can't be expressed directly.
For example, how do you group by an expression?

select count(*), substring(b from 1 for 5)
group by substring(b from 1 for 5)
=> error, group only accepts field names.

This example would give you the number of tables that start with
rdb$<character>, assuming it's applied to rdb$relations. If it were allowed,
you would see the equivalent of this:

SQL> create view v(s) as select substring(rdb$relation_name from 1 for 5)
from rdb$relations;
SQL> select s, count(s) from v group by s;

S COUNT
=============================== ============

RDB$C 3
RDB$D 2
RDB$E 1
RDB$F 7
RDB$G 1
RDB$I 2
RDB$L 1
RDB$P 3
RDB$R 5
RDB$S 1
RDB$T 4
RDB$U 1
RDB$V 1

Probably you will argue that you write a selectable stored proc and voila.

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing