special marker and keyword in SQL indicating that something has no value
Factsheet
If you want to add a and b and either may be null, you could use coalesce, which returns the first non-null parameter you pass it:
coalesce(a+b, a, b)
So in this case, if neither parameter is null, it will return the sum. If only b is null, it will skip a+b and return a. If a is null, it will skip a+b and a and return b, which will only be null if they are both null.
If you want the answer to be 0 rather than null if both a and b are null, you can pass 0 as the last parameter:
coalesce(a+b, a, b, 0)
Do consider @erwins answer - null might not be the right thing to be using.
I accomplished it this way:
coalesce("Column1",0.00) + coalesce("Column2",0.00)
I'm working with front end high level execs.... They don't understand why NULL and 0 aren't handled the same way.
In my case it works, just replacing NULLs with 0.00... may not in all though :)
Hello everyone, I am new in data analytics and I am trying to sum 2 columns and create a new one with the total value, the thing is that I am having null values on the new column despite of having valid values on at least one of the summing fields, does anyone know what could be happening? Thanks in advance
Neither IF statement is true. NULL is neither equal to something nor not equal to something. Something either IS NULL or IS NOT NULL.
Agree with what everyone else has already said. Simply commenting from another angle, if you try setting ansi_nulls to off, you may get what you expected:
set ansi_nulls off
if 1 = null
select 'a'
else
select 'b' -- Returned
if 1 != null
select 'a' -- Returned
else
select 'b'
More info from Books Online:
When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.
That's ansi_nulls off explained. However, don't be tempted to simply switch it off because:
In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Follow the below recommendation instead:
For a script to work as intended, regardless of the ANSI_NULLS database option or the setting of SET ANSI_NULLS, use IS NULL and IS NOT NULL in comparisons that might contain null values.
if 1 is null
select 'a'
else
select 'b' -- Returned
if 1 is not null
select 'a' -- Returned
else
select 'b'
You can use a CASE statement.
SELECT
CASE WHEN currate.currentrate IS NULL THEN 1 ELSE currate.currentrate END
FROM ...
You can use COALESCE:
SELECT orderhed.ordernum,
orderhed.orderdate,
currrate.currencycode,
coalesce(currrate.currentrate, 1) as currentrate
FROM orderhed
LEFT OUTER JOIN currrate
ON orderhed.company = currrate.company
AND orderhed.orderdate = currrate.effectivedate
Or even IsNull():
SELECT orderhed.ordernum,
orderhed.orderdate,
currrate.currencycode,
IsNull(currrate.currentrate, 1) as currentrate
FROM orderhed
LEFT OUTER JOIN currrate
ON orderhed.company = currrate.company
AND orderhed.orderdate = currrate.effectivedate
Here is an article to help decide between COALESCE and IsNull:
http://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/