SQL SELECT SUM of columns is returning incorrect values

Question

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

Gordon Linoff · Accepted Answer · 2018-01-10 07:22:34Z

3

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

answered Jan 10, 2018 at 7:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL SELECT SUM of columns is returning incorrect values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related