Oracle SQL return column calculated from existing columns

select 
t1.name, 
(select count(*) from t2 where t2.name = t1.name) X, 
(select count(*) from t3 where t3.name = t1.name) Y
from t1;

I have the above SQL query which returns the columns name, X and Y. I would like to return one more column (lets call it Z) which gives the value of X/Y but not too sure how to do this – I tried writing (X/Y) Z but no luck – any suggestions?

Answer

• Use CTE

with data as (

select 
t1.name, 
(select count(*) from t2 where t2.name = t1.name) X, 
(select count(*) from t3 where t3.name = t1.name) Y
from t1
)

select 
data.*,
data.x/data.y as new_calculation
from data