0

I have this table that i will populate with random figures:

|--week--||-2016-||-2017-|
|  1     || 26734||6314916|
|  2     || 64565||9876768|
|  3     || 32243||9976757|

what I want to do is create a fourth column that is basically a variance of these numbers. I created the script below which I know works as I had created it from for another table, there's no difference between these tables apart from the figures in them.

 select CAST(ROUND(sum (([2017]) /(([2016]))-1)*100, 0) as NUMERIC(36,0))  as [variance%]

from table2

I get the below

|--week--||-2016-||-2017-| variance%
|  1     || 26734||6314916|0
|  2     || 64565||9876768|0
|  3     || 32243||9976757|0

why am i getting zeros when the other table i had delivers the actual results for variances?

VS1SQL
  • 135
  • 2
  • 13
  • 2
    Use `[2017]*1.0` instead of just `[2017]` – Lamak Aug 02 '17 at 13:34
  • 1
    _Why_ > Because you are doing integer calculus. Convert or cast the integer columns to float or decimal type as needed first. – TT. Aug 02 '17 at 13:35
  • 1
    Possible duplicate of [How to get a float result by dividing two integer values?](https://stackoverflow.com/questions/11719044/how-to-get-a-float-result-by-dividing-two-integer-values) – S3S Aug 02 '17 at 13:36

1 Answers1

1

All you need to do is mould your INT into float value. Something like this :

select 
CAST(ROUND(sum (([2017]*1.0) /(([2016]))-1)*100, 0) as NUMERIC(36,0))  as [variance%]

read this stack exchange answer to know more about this.

Prabhat G
  • 2,974
  • 1
  • 22
  • 31