Solved

# Add multiple columns together in sql statement

Posted on 2005-04-19
Medium Priority
1,091 Views
Is there a way to add multiple columns together in a SQL statement and if a field equals null then add it as zero? This is the sql staement I have so far:
------------------------------------------
SELECT     SUM(CAST(itemcost1 AS decimal(18, 2))) + SUM(CAST(itemcost2 AS decimal(18, 2))) + SUM(CAST(itemcost3 AS decimal(18, 2)))
+ SUM(CAST(itemcost4 AS decimal(18, 2))) + SUM(CAST(itemcost5 AS decimal(18, 2))) + SUM(CAST(itemcost6 AS decimal(18, 2)))
+ SUM(CAST(itemcost7 AS decimal(18, 2))) + SUM(CAST(itemcost8 AS decimal(18, 2))) + SUM(CAST(itemcost9 AS decimal(18, 2)))
+ SUM(CAST(itemcost10 AS decimal(18, 2))) AS 'Sales without tax/shipping', CONVERT(varchar, orderdate, 101) AS [Date], COUNT(*) AS Orders,
SUM(CAST(transactionamount AS decimal(18, 2))) AS 'Sales with tax/shipping'
FROM         table1
GROUP BY orderdate
------------------------------------------
The problem is the itemcostX columns are varchar and some of the values contained in the columns are null, so when I add the columns together they produce a null result. Anysuggestions? Here is a another example of what I would like:

table contains the following columns data:
a     b     c     d
-----------------
1     null  2     1
1     3     null  null
null  5     null  2

I need a SQL statement to add columns a + b + c + d together for each row. So using the above data should provide a total of 15. Maximum points rewarded to the first working example you provide.
0
• 2

LVL 51

Expert Comment

ID: 13816718
ISNULL ( check_expression , replacement_value )

0

LVL 51

Accepted Solution

Steve Bink earned 2000 total points
ID: 13816728
SELECT     SUM(CAST(ISNULL(itemcost1,0) AS decimal(18, 2))) + SUM(CAST(ISNULL(itemcost2,) AS decimal(18, 2))) + etc...
0

LVL 5

Expert Comment

ID: 13816744
Just do a NULL check

SELECT     SUM(ISNULL(CAST((itemcost1 AS decimal(18, 2)), 0))
+ SUM(ISNULL(CAST(itemcost2 AS decimal(18, 2)),0))
+ SUM(ISNULL(CAST(itemcost3 AS decimal(18, 2)),0))
+ SUM(ISNULL(CAST(itemcost4 AS decimal(18, 2)),0))
+ ...
+ SUM(ISNULL(CAST(itemcost10 AS decimal(18, 2)),0)) AS 'Sales without tax/shipping',
CONVERT(varchar, orderdate, 101) AS [Date], COUNT(*) AS Orders,
SUM(CAST(transactionamount AS decimal(18, 2))) AS 'Sales with tax/shipping'
FROM         table1
GROUP BY orderdate
0

LVL 28

Expert Comment

ID: 13816832
Try this:

SELECT SUM(ISNULL(CAST(ItemCost1 AS DECIMAL(18, 2)), 0) +
ISNULL(CAST(ItemCost2 AS DECIMAL(18, 2)), 0) +
ISNULL(CAST(ItemCost3 AS DECIMAL(18, 2)), 0) +
ISNULL(CAST(ItemCost4 AS DECIMAL(18, 2)), 0) +
ISNULL(CAST(ItemCost5 AS DECIMAL(18, 2)), 0) +
ISNULL(CAST(ItemCost6 AS DECIMAL(18, 2)), 0) +
ISNULL(CAST(ItemCost7 AS DECIMAL(18, 2)), 0) +
ISNULL(CAST(ItemCost8 AS DECIMAL(18, 2)), 0) +
ISNULL(CAST(ItemCost9 AS DECIMAL(18, 2)), 0) +
ISNULL(CAST(ItemCost10 AS DECIMAL(18, 2)), 0))  AS 'Sales with tax/shipping',
CONVERT(varchar, orderdate, 101) AS [Date]
FROM Table1
GROUP BY CONVERT(varchar, orderdate, 101)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
###### Suggested Courses
Course of the Month15 days, 14 hours left to enroll