I have a table like this:

I'm looking to find the proportion of the bond portfolio maturing earlier than date X. The answer should be returned as a single value.

For example, the proportion of the portfolio maturing earlier than 2024-06-30 is (24.76 26.5 34.19 35.99) / (24.76 26.5 34.19 35.99 40.7 40.8) = 59.84%

CodePudding user response：

You can use the CASE expression :

SELECT SUM(CASE WHEN maturity_date < '2024-06-30' THEN bond_face_value ELSE 0 END) / SUM( bond_face_value)
FROM myTable;

CodePudding user response：

with cte as(select sum(bond_face_value) sum_calc 
            from mytable 
            where maturity_date <= '2024-06-30')
select concat(round((max(t2.sum_calc)/sum(t1.bond_face_value)) * 100, 2), '%')
from mytable t1, cte t2

OR

select concat(round((select sum(bond_face_value) sum_calc 
            from mytable 
            where maturity_date <= '2024-06-30')
       /sum(bond_face_value)*100, 2), '%')
from mytable 

DEMO

CodePudding user response：

try with below code.You need to filter the required data first and then divide with sum.

select ((sum(bond_face_value) filter (where maturity_date<'2024-06-30'))/(sum(bond_face_value))*100 from tbl_name