Search code examples
sqloracle-databaserowcount

select value into viarble in Oracle SQL

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.

Solution

  • 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;