In Focus

Sum of Columns of Two Tables Into One Column in SQL Server 2012

This article will give you an idea of how to add columns of two tables into one column and also defines how to handle a null value in SQL Server.

This article will give you an idea of how to add columns of two tables into one column and also defines how to handle a null value in SQL Server. There is no need to write an insert query again and again; you can do it using a single query. Here, I have constructed a query that provides a solution for adding columns of multiple tables into one column. Let's take a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

We create two tables with the same id columns. The first table is named UserTable with the columns ID and Name and another table named TableUser with the columns ID and UserName.

The tables looks as in the following:

UserTable

UserTable-in-SQL-Server.jpg

TableUser

TableUser-in-SQL-Server.jpg

In the preceding, tables id is the common columns. We add ID columns of both tables, for example:

2+2=4
55+5=10

Now the following is the simple example to add columns of multiple tables into one column using a single Full join:

select T1.ID as TableUserID, T2.id as TableUserID,T1.Id+T2.Id as AdditonResult

from UserTable as T1

Full join tableuser as T2

on T1.name = T2.UserName

 

Now Press F5 to run the query and select the query to see the result.

 

Solution-of-NuLL-Value-in-SQL-Server.jpg

 

Problem

 

In the preceding image we see UserTable has 4 columns and table TableUser has 2 columns. So it uses a NULL value as the result. Such as:

 

9+ NULL=NULL

11+NULL=NULL

 

Solution

 

You can use a SQL "ISNULL" or "COALESCE" function to handle a NULL value. These functions are explained below.

ISNULL() Function

The "ISNULL()" function is used to replace a NULL with the specified replacement value. This function contains only two arguments.

Syntax

ISNULL (check_exp, change_value)

Example

Declare @name varchar=null

select isnull(@name, '0') AS ISNULLResult

Output

Isnull-function-in-SQL-Server.jpg

Coalesce() Function

The "Coalesce()" function returns the first non-null value among its arguments. This function doesn't limit the number of arguments, but they must all be of the same data type.

Syntax

COALESCE ( expression [ ,...n ] ) 

Example

Declare @name varchar=null

select COALESCE(@name, '0') AS COALESCEResult

Output

coalesce-function-in-SQL-Server.jpg

Now we use the ISNULL Function to convert a NULL value to 0 to find the sum of all columns. The following is the simple example to add columns of multiple tables into one column using a single full join using the ISNULL Function.

 

SELECT ISNULL(T1.ID,0) as UserTableID  , isnull(T2.Id,0) as TableUserID, ISNULL(T1.Id,0)+ isnull(T2.Id,0) as AdditonResult

FROM UserTable T1

FULL JOIN tableuser T2

ON T1.name = T2.UserName

 

Now Press F5 to run the query and select the query to see the result.

 

NULL-Value-Problem-in-SQL-Server.jpg