How to sum multiple columns in SQL? – Code Example

In this article, I will show you the ways to sum multiple columns in SQL. We will discuss how to prevent NULL columns from polluting the output.

Code Examples

1. Sum of multiple columns using SUM() function

SELECT  ID, SUM(VALUE1 + VALUE2)
FROM    tableName
GROUP   BY ID

Where VALUE1 and VALUE2 are the two columns. This will return the total sum of values in both the columns having same ID.

2. Getting total sum of multiple columns

In previous code we calculated the sum of two columns of rows with same ID. Now we will calculate the total sum of both columns of all the rows.

SELECT  SUM(VALUE1 + VALUE2)
FROM    tableName

3. Sum of multiple columns of each row separately

SELECT  ID, VALUE1 + VALUE2
FROM    tableName

Here we don’t need to use SUM() function because we can add values of two columns using + only.

4. Set sum of two columns as value of third column

In this code we will set the value of 3rd column as the sum of first two columns.

UPDATE tableName 
SET VALUE3 = VALUE1 + VALUE2

5. Convert NULL to 0 using coalesce

coalesce returns the first non-null value. So, if first argument is column and second is 0 then it will return column if column is not null or 0 if column is null. This way null will convert to 0.

Let’s sum the columns but first sanitize them using coalesce –

SELECT  ID, SUM(coalesce(VALUE1, 0) + coalesce(VALUE2, 0))
FROM    tableName
GROUP   BY ID

6. Convert NULL to 0 using ISNULL()

Just like coalesce function, ISNULL() could also be used to sanitize the column.

SELECT  ID, SUM(ISNULL(VALUE1, 0) + ISNULL(VALUE2, 0))
FROM    tableName
GROUP   BY ID

7. Convert columns to integer before sum

It’s good to convert columns to integers if you want integer sum. We can use convert() function for it.

SELECT  ID, SUM(convert(int, VALUE1) + convert(int, VALUE2))
FROM    tableName
GROUP   BY ID