How to sum multiple columns in sql

Article Mohit Mozumder

Problem:

Sometimes we need to sum multiple columns in sql, lets learn how to do it.


Solution :

Now we try to learn how to multiple columns in sql.

Lets see the table below.

ID  VALUE1  VALUE2   VALUE3
===========================
1     1       2         3
1     2       2         1
2     3       4         2
2     4       5         1

 If we use the sql...

SELECT  ID, VALUE1 + VALUE2 +VALUE3
FROM    TableName

will result

ID   VALUE1 + VALUE2+ VALUE3
1             6
1             5
2             9
2             10
Here, SUM function work row wise and give the sum of each row like the example.

We can use the sum function in this way too...

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

result

ID, SUM(VALUE1), SUM(VALUE2) SUM(VALUE3)                  
1     3             4           4
2     7             9           3

here we use "group by id" so SUM function will work on the id and calculate the sum of same id's rows. Like here are two rows for id 1 . so it sum the two rows  and give the result.


again if we use.....


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

will result

ID, SUM(VALUE1 + VALUE2 + VALUE3)
1               11
2               19

Here we used "GROUP BY ID" so SUM function will work on the id and calculate the sum of same id's. Like here are two rows for id 1 . so it will sum the three values of each row and then sum the row with same id. Like two row have id 1 so it shows the answer of summation of those two rows with same id.


Note:

The above solutions can give error if there is any null values. If there is any null value you can use this....

select ID, (coalesce(VALUE1 ,0) + coalesce(VALUE2 ,0) + coalesce(VALUE3 ,0))
as Total from TableName