Createing SQL view with multiple subqueries

Question

I have simple query which works perfectly:

(select distinct ACTIVE_MEMBERS, BRANCH_MEMBERS from
    ( select count(*) from pink_floyd pf ) as ACTIVE_MEMBERS,
    ( select count(*) from pink_floyd pf ) as BRANCH_MEMBERS
);

And from above query I want to create view - Im doing it like this:

create or replace view v_monthly_data AS
select data.active_members, data.branch_members
from (select distinct ACTIVE_MEMBERS, BRANCH_MEMBERS from
    ( select count(*) from pink_floyd pf ) as ACTIVE_MEMBERS,
    ( select count(*) from pink_floyd pf ) as BRANCH_MEMBERS
) as data;

But its seems to be incorrect. I would be gratefull for explanation me right way of creating this view. Also I would prefer not to create multiple views, and then joining them.

Answer 1

Your simple query is not valid Oracle syntax as AS is not a valid keyword before an alias for a table (or an inline view). You appear to want to use column aliases and not table aliases.

A correct query would be:

select distinct ACTIVE_MEMBERS, BRANCH_MEMBERS
from   ( select count(*) as ACTIVE_MEMBERS from pink_floyd pf ),
       ( select count(*) as BRANCH_MEMBERS from pink_floyd pf );

Which could be simplified to:

select count(*) AS active_mambers,
       count(*) AS branch_members
from   pink_floyd;

Then you can create a view:

CREATE VIEW v_monthly_data (active_members, branch_members) AS
select count(*),
       count(*)
from   pink_floyd;

or

CREATE VIEW v_monthly_data AS
select count(*) AS active_members,
       count(*) AS branch_members
from   pink_floyd;