Need a query for subtraction and division?

Question

In my table column are like these

msd_mon, msd_yea, msd_sva ... I want a query

SELECT
         (((        SELECT SUM(msd_sva)
        FROM  msdfc00 
        WHERE    msd_yea  IN
            (
            SELECT DISTINCT msd_yea
            FROM  msdfc00 
            )
)-(     SELECT SUM(msd_sva)
        FROM  msdfc00 
        WHERE    msd_yea  = '2016'
)* 100) / (     SELECT SUM(msd_sva)
        FROM  msdfc00 
        WHERE    msd_yea  = '2016'
)) AS thePerc,
         msd_yea
FROM  msdfc00 
WHERE    msd_fco  = 'KF'
 AND    msd_mon  = '11'
GROUP BY  msd_yea 

that is I want to substract sum of msd_sva for period of msd_mon for the year 2017 from sum of msd_sva for period of msd_mon for the year 2016 . My Base year is 2016. I got only 4 years that is upto 2018. so calulation will be like these

for year 2016 value will be 0 for year 2017 it will be ((2017 - 2016)* 100 \2016) for year 2017 it will be ((2018 - 2016)* 100 \2016) for year 2017 it will be ((2019 - 2016)* 100 \2016)

any help ?

Answer 1

Use conditional aggregation:

select sum(case when msd_yea = 2016 then msc_sva end) as sva_2016,
       sum(case when msd_yea = 2017 then msc_sva end) as sva_2017,
       sum(case when msd_yea = 2018 then msc_sva end) as sva_2018,
       sum(case when msd_yea = 2019 then msc_sva end) as sva_2019       
from msdfc00 
where msd_fco  = 'KF' and msd_mon  = '11';

You can use a subquery to do your final calculations:

select 0 as val_2016,
       (sva_2017 - sva_2016) * 100 / sva_2016 as val_2017,
       . . .
from (select sum(case when msd_yea = 2016 then msc_sva end) as sva_2016,
             sum(case when msd_yea = 2017 then msc_sva end) as sva_2017,
             sum(case when msd_yea = 2018 then msc_sva end) as sva_2018,
             sum(case when msd_yea = 2019 then msc_sva end) as sva_2019       
      from msdfc00 
      where msd_fco  = 'KF' and msd_mon  = '11'
     ) m;