select value into viarble in Oracle SQL

Question

I need some help with a query.

I want to select row count value from a table, and then to use the value in a different query. For example:

@rowcount = select count(*) from MyTable

select A*@rowcount, B/@rowcount
from MyOtherTable

Can someone show my what is the correct syntax? I need to use @rowcount a lot of times so i prefer to calculate it only once.

Answer 1

In Oracle you can't mix procedural code and regular SQL like that.

But if you use this:

select a / (select count(*) from mytable), 
       b / (select count(*) from mytable)
from myothertable;

Oracle will evaluate the count(*) only once. There is no need to store the value somewhere to improve performance.

If you want, you could move this into a common table expression:

with row_count as (
   select count(*) as numrows
   from mytable
)
select a / (select numrows from row_count), 
       b / (select numrows from row_count)
from myothertable;