Make a query with result into variable and use it in other query-CodePudding

I made a query that count data and return the number of posts, I put it INTO variable as follows :

declare
  cnt_posts int;
  begin
      select count(*) as cnt into cnt_posts from posts;
      /*for exapmle this will return 300*/
 end;
    /

so I would like to use result which is inside this variable in others queries as follows:

other query :

select posts.id, round(100*(count(my_data) / sum(:cnt_posts) over()),2) as percentage
from posts
where posts.category_id = 3
group by posts.id

so I want to use cnt_posts in other query to calculate percentage

CodePudding user response：

Use a bind variable:

VARIABLE cnt_posts NUMBER;

BEGIN
  SELECT count(*)
  INTO   :cnt_posts
  FROM   posts;
END;
/

select posts.id,
       round(
         100*count(my_data)/:cnt_posts,
         2
       ) as percentage
from   posts
where  posts.category_id = 3
group by posts.id

And then, in SQL Developer, run it as a script using F5.

Alternatively, you could just merge it all into a single query:

select posts.id,
       round(
         100*count(my_data)/(SELECT COUNT(*) FROM posts),
         2
       ) as percentage
from   posts
where  posts.category_id = 3
group by posts.id