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 ?

See: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Strawberry, Dec 05 '19 at 12:24

Gordon Linoff · Accepted Answer · 2019-12-06T12:06:32.347

3

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;

edited Dec 06 '19 at 12:06

answered Dec 05 '19 at 12:16

Gordon Linoff

1,242,037
58
646
786

select 0 as val_2016, (sva_2017 - sva_2016) * 100 / sva_2016 as val_2017, (sva_2018 - sva_2016) * 100 / sva_2016 as val_2018, .. from (select sum(case when msd_yea = 2016 then msd_sva end) as sva_2016, sum(case when msd_yea = 2017 then msd_sva end) as sva_2017, sum(case when msd_yea = 2018 then msd_sva end) as sva_2018, sum(case when msd_yea = 2019 then msd_sva end) as sva_2019 from msdfc00 where msd_fco = 'KF' and msd_mon = '11' ) m; invalid column sva_2017 – Gopipuli Dec 06 '19 at 03:50
thank you @Gordon Linoff .. I get an error says invalid column .. – Gopipuli Dec 06 '19 at 03:52
2

@Gopipuli . . . The columns names are taken from your question, although there might be typos. – Gordon Linoff Dec 06 '19 at 12:07
thanks Gordon Linoff I just split up the query to two tabels, it give me the result. I am not an expert in query. Thank you for the valuable time. If time permit I want one more help nowthe above results are getting as rows Will it get in column wise ? that is under year column ? – Gopipuli Dec 13 '19 at 08:06
@Gopipuli . . . I don't fully understand what you are asking in the comment. Can you ask another question, with sample data and desired results to be sure that the explanation is clear? – Gordon Linoff Dec 13 '19 at 11:20

Need a query for subtraction and division?

1 Answers1