0

I have the following SQL Query:

SELECT
    a.IA
  , a.PC
  , a.LC
  , a.CXPevalCnt
  , ((a.IA / nullif(a.CXPevalCnt,0))*0.35) as IAcalc
  , ((a.LC / nullif(a.CXPevalCnt,0))*0.5) as legacalc
  , ((a.PC / nullif(a.CXPevalCnt,0))*0.15) as pccalc
  , (
      ((a.IA / nullif(a.CXPevalCnt,0))*0.35)
    + ((a.LC / nullif(a.CXPevalCnt,0))*0.5)
    + ((a.PC / nullif(a.CXPevalCnt,0))*0.15)
  ) as Compliance 
FROM 
(
  SELECT
      SUM(IA) as IA
    , SUM(PC) as PC
    , SUM(LC) as LC
    , SUM(CXPevalcount) as CXPevalCnt
  FROM
    tblPLOps_Data
) as A

Which returns the following data:

IA PC   LC   CXPevalCnt  IAcalc  legacalc pccalc  Compliance

15 12   15      15        0.35    0.5        0     0.85

If PC is 12 and CXPevlacnt is 15 I cannot figure out why in my query when I add them together cxpevalcount nulls out but only for PC and not when used with IA or LC. I have sliced this query many different ways and can't figure out the issue

any ideas?

Pred
  • 8,789
  • 3
  • 26
  • 46
user7668852
  • 59
  • 1
  • 10
  • 1
    Can you please post some sample data to demonstrate your issue. [How to post a T-SQL Question](http://www.sqlservercentral.com/articles/Best+Practices/61537/). – Thom A Jan 12 '18 at 14:32
  • `Integer/Integer = Integer` – Pரதீப் Jan 12 '18 at 14:39
  • 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) – underscore_d Jan 12 '18 at 14:48

1 Answers1

1

Because you are doing integer division. 12/15 = 0. If you want to do accurate math you need to multiply one of them by 1.0 or convert to force sql to do numeric division.

((a.PC * 1.0) / nullif(a.CXPevalCnt, 0)) * 0.15 as pccalc

You would need to do the same for your other columns as well.

Sean Lange
  • 33,028
  • 3
  • 25
  • 40