A VIEW definition that makes use of a derived table:

create view v_foo 
as 
select f.*, 
       b.cnt 
from foo f 
  join ( 
     select fid, count(*) as cnt 
     from bar 
     group by fid 
  ) b on b.fid = foo.id;

Back to the SQL Feature Comparison