CODEWITHSUNDEEP
sqlsql-server
Sebastian
Sebastian

Reputation: 4811

SQL SELECT SUM of columns is returning incorrect values

I am using an SQL select and a part of query is below 

    SELECT coalesce(C2.PRE,0) as PRE,coalesce(C4.PREX,0) as PREX,
            coalesce(C2.AFTER,0) as AFTER,  coalesce(C4.AFTERX,0) as AFTERX

it returns 4 columns 

    1   0   0   0

I tried to add 3 more columns to the SQL SELECT , and these 3 columns is to find some SUM values based on the above columns 

    PRESUM =(PRE+PREX) = 1+0=1
    AFTERSUM =(AFTER+AFTERX) =0+0=0
    TOTAL =PRESUM+AFTERSUM)= 1+0=1

The new SQL is  

    SELECT coalesce(C2.PRE,0) as PRE, coalesce(C4.PREX,0) as PREX,
          coalesce(C2.AFTER,0) as AFTER,  coalesce(C4.AFTERX,0) as AFTERX,  
           coalesce((PRE+PREX),0) as PRESUM
           coalesce((AFTER+AFTERX),0) as AFTERSUM
           coalesce((PRESUM+AFTERSUM),0) as TOTAL

and result returning is 

    1   0   0   0   0   0   0 ,

but the last 3 columns [PRESUM,AFTERSUM,TOTAL] should return 1 0 0. Anything wrong in the way i calculated the sum

Upvotes: 0

Views: 67

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269943

When doing arithmetic, NULL results in a NULL value. So, you probably want this:

SELECT coalesce(C2.PRE,0) as PRE,coalesce(C4.PREX,0) as PREX,
       coalesce(C2.AFTER,0) as AFTER, coalesce(C4.AFTERX,0) as AFTERX,
       ( coalesce(PRE, 0) + coalesce(PREX, 0) ) as PRESUM,
       ( coalesce(AFTER, 0) + coalesce(AFTERX, 0) ) as AFTERSUM,
       ( coalesce(PRESUM, 0) + coalesce(AFTERSUM, 0) ) as TOTAL

Upvotes: 3

Related Questions