Convert NULL to zero

One table I am working with has several money columns. Some are null. I need
to add the columns, but the result is NULL since some of the columns are
NULL.
How can I write the query to convert to zeroes?
Thanks.SELECT COLAESCE(col1,0) + COALESCE(col2,0) + ...
FROM Sometable
--
David Portas
--
Please reply only to the newsgroup
--
"Paul" <nospam@.please.com> wrote in message
news:%23nSQbLK1DHA.2336@.TK2MSFTNGP09.phx.gbl...
> One table I am working with has several money columns. Some are null. I
need
> to add the columns, but the result is NULL since some of the columns are
> NULL.
> How can I write the query to convert to zeroes?
> Thanks.
>|||Thank you. I'll try that.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:AL-dnQNnTNpeqWai4p2dnA@.giganews.com...
> SELECT COLAESCE(col1,0) + COALESCE(col2,0) + ...
> FROM Sometable
> --
> David Portas
> --
> Please reply only to the newsgroup|||You can also do it with ISNULL:
SELECT SUM(ISNULL(col1,0)), SUM(ISNULL(col2,0)) + ...
FROM TestTable
--
Rohtash Kapoor
http://www.sqlmantra.com
"Paul" <nospam@.please.com> wrote in message
news:OtDfAjK1DHA.2308@.TK2MSFTNGP11.phx.gbl...
> Thank you. I'll try that.
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:AL-dnQNnTNpeqWai4p2dnA@.giganews.com...
> > SELECT COLAESCE(col1,0) + COALESCE(col2,0) + ...
> > FROM Sometable
> >
> > --
> > David Portas
> > --
> > Please reply only to the newsgroup
>|||Since the SUM aggregate ignores NULLs anyway you will save some processor
cycles by putting ISNULL (or COALESCE) outside the SUM. This deals with the
specific case where all values in the column are NULL.
SELECT ISNULL(SUM(col1),0), ISNULL(SUM(col2),0) + ...
FROM TestTable
COALESCE is an ANSI Standard SQL function which is why I usually prefer to
use it in preference to the proprietary ISNULL function.
--
David Portas
--
Please reply only to the newsgroup
--|||You are very right.
Instead of this:
SELECT SUM(ISNULL(col1,0)), SUM(ISNULL(col2,0)) + ...
FROM TestTable
We should write:
SELECT ISNULL(SUM(col1),0), ISNULL(SUM(col2),0) + ...
FROM TestTable
However, I prefer to use ISNULL than COALESCE because I can type it quickly
(...just kidding..)
--
Rohtash Kapoor
http://www.sqlmantra.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:9O-dnaPUQsGe32aiRVn-vA@.giganews.com...
> Since the SUM aggregate ignores NULLs anyway you will save some processor
> cycles by putting ISNULL (or COALESCE) outside the SUM. This deals with
the
> specific case where all values in the column are NULL.
> SELECT ISNULL(SUM(col1),0), ISNULL(SUM(col2),0) + ...
> FROM TestTable
> COALESCE is an ANSI Standard SQL function which is why I usually prefer to
> use it in preference to the proprietary ISNULL function.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Since the SUM aggregate ignores NULLs anyway you will save some
> processor cycles by putting ISNULL (or COALESCE) outside the SUM. This
> deals with the specific case where all values in the column are NULL.
> SELECT ISNULL(SUM(col1),0), ISNULL(SUM(col2),0) + ...
> FROM TestTable
> COALESCE is an ANSI Standard SQL function which is why I usually prefer to
> use it in preference to the proprietary ISNULL function.
On the other hand, if you do this and run with ANSI_WARNINGS enabled, you
will get the message "Warnings: null eliminated from aggregate".
(ANSI_WARNINGS is enabled for default, unless you are using DB-Library.)
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp