?
Solved

Add multiple columns together in sql statement

Posted on 2005-04-19
4
Medium Priority
?
1,091 Views
Last Modified: 2011-04-14
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
Comment
Question by:bradylanter
  • 2
4 Comments
 
LVL 51

Expert Comment

by:Steve Bink
ID: 13816718
ISNULL ( check_expression , replacement_value )

0
 
LVL 51

Accepted Solution

by:
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

by:obahat
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

by:rafrancisco
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question