SQL sum different columns into one statement

Question

I am building an application that uses a SQL statement to sum three columns. Below is a sample table:

Table1
column1     column2  column3
NULL        30.00    NULL
60.00       NULL     NULL
NULL        10.00    NULL
NULL        NULL     15.00

I want to sum column1, column2, and column3 into one statement. I want the result to be 115.00 (30.00 + 60.00 + 10.00 + 15.00). The table can have data in one of the three columns, but never in any two.

This is what I have so far:

SELECT ISNULL(sum(column1),ISNULL(sum(column2),sum(column3)) as amount FROM Table1

The result is something not remotely close.

Possible duplicate of SQL: sum 3 columns when one column has a null value? — Edward
– Edward, Commented Jan 13, 2017 at 3:16

Gordon Linoff · Accepted Answer · 2017-01-13 03:17:43Z

1

In your case, you can do:

select sum(column1) + sum(column2) + sum(column3)
from table1 t;

Because each column has at least one value, the individual sums will not be NULL, so this will give the expected value.

To be safe, you could use coalesce():

select coalesce(sum(column1), 0) + coalesce(sum(column2), 0) + coalesce(sum(column3), 0)
from table1 t;

answered Jan 13, 2017 at 3:17

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Comments

Vamsi Prabhala · Accepted Answer · 2017-01-13 03:08:08Z

1

Use coalesce to assign 0 when there is null in the column and then sum the values.

SELECT SUM(coalesce(column1,0)+coalesce(column2,0)+coalesce(column3,0)) as amount 
FROM Table1

answered Jan 13, 2017 at 3:08

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Comments

Rai Vu · Accepted Answer · 2017-01-13 03:10:52Z

1

The COALESCE function will also work. In the given example:

SELECT sum(COALESCE(column1,0))
          + COALESCE(column2,0)
          + COALESCE(column3,0)
    AS TOTAL FROM Table1

answered Jan 13, 2017 at 3:10

Rai Vu

1,6151 gold badge23 silver badges35 bronze badges

Collectives™ on Stack Overflow

SQL sum different columns into one statement

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related