Today is Monday letting us start this week with interesting puzzle. Yesterday I had also posted a quick question here: SQL SERVER – T-SQL Scripts to Find Maximum between Two Numbers
Run following code:
SELECT SUM(data) FROM (SELECT NULL AS DATA) t
It will throw following error.
Msg 8117, Level 16, State 1, Line 1
Operand data type void type is invalid for sum operator.
I can easily fix if I use ISNULL Function as displayed following. ISNULL function replaces NULL with the specified replacement value. The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different. NULLIF function returns a null value if the two specified expressions are equal. NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.
SELECT SUM(data) FROM (SELECT ISNULL(NULL,0) AS DATA) t
The above script will not throw an error. However, there is one more method how this can be fixed.
Can you come up with another method which will not generate error?
Reference: Pinal Dave (https://blog.sqlauthority.com)
33 Comments. Leave new
SELECT SUM(isnull(data,0))
FROM (SELECT NULL AS DATA) t
SELECT SUM(data)
FROM (SELECT COALESCE(NULL,0) AS DATA) t
OR
SELECT SUM(Coalesce(data,0))
FROM (SELECT NULL AS DATA) t
Cheers
Shekhar Teke
I think your isnull(null,0) is cheating, because your really doing a SELECT SUM(data) FROM (SELECT 0 AS DATA) t, which works without a problem.
Select SUM(null) also fails, which gets us closer to the root cause – SQL Server can’t determine the data type of NULL.
If we cast null to int using SELECT SUM(data) FROM (SELECT cast(NULL as int) AS DATA) t , we get rid of the error, however the result is now NULL.
Another method that will return NULL is: SELECT SUM(data) FROM (SELECT 0 + NULL AS DATA)
I guess the answer really depends on how you define “fixed”.
We can also move the ISNULL into the sum eg, SELECT SUM(isnull(data,0)) FROM (SELECT NULL AS DATA) t.
Other solutions would be isNull replacements, eg, CASE, COALESCE, which both ‘fix’ the problem, and return your original value of 0.
Dave
SELECT SUM(data)
FROM (SELECT convert(int, NULL) AS DATA) t
Hi Pinal,
We can use COALESCE to fix this, Please see below query
SELECT SUM(data)
FROM (SELECT COALESCE(NULL,0) AS DATA) t
Regards
Arunraj Chandrasekaran
SELECT SUM(data)
FROM (SELECT COALESCE(NULL,0) AS DATA) t
SELECT SUM(data)
FROM (SELECT COALESCE(null,0) AS DATA) t
SELECT SUM(ISNULL(data,0))
FROM (SELECT NULL AS DATA) t
GO
SELECT SUM(COALESCE(data,0))
FROM (SELECT NULL AS DATA) t
GO
SELECT SUM(data)
FROM (SELECT COALESCE(NULL,0) AS DATA) t
SELECT SUM(data)
FROM (SELECT CASE WHEN NULL is NULL then 0 END AS DATA) t
Similarly we can handle the NULL as
SELECT SUM(isnull(data,0))
FROM (SELECT NULL AS DATA) t
The Following could be the other alternatives –
SELECT SUM(data)
FROM (SELECT COALESCE(NULL,0) AS DATA) t
SELECT SUM(ISNULL(data,0))
FROM (SELECT NULL AS DATA) t
SELECT SUM(COALESCE(data,0))
FROM (SELECT NULL AS DATA) t
Hi pinal,
I think we can use COALESCE like
select SUM(data)
from (select COALESCE(null,0) as data) t
SUM can be used with numeric columns only. Null values are ignored.
SELECT SUM(isnull(data,0))
FROM (SELECT NULL AS DATA) t
oR
SELECT SUM(cast(data as Int))
FROM (SELECT NULL AS DATA) t
HI Pinal
We can use below also
SELECT SUM(data*1)
FROM (SELECT NULL AS DATA) t
Hi Pinal,
Can we use this method :
SELECT SUM(data)
FROM (SELECT Cast(NULL as int) AS DATA) t
Hello,
I believe that you just need to convert the null void to a value of type
tinyint
smallint
int
bigint
decimal category (p, s)
money and smallmoney category
float and real category
using an explicit cast (or convert)
or
do something like isnull coalesce when an implicit cast is applied.
SELECT SUM(data)
FROM (SELECT NULL AS DATA union all SELECT 0 AS DATA) t
select cast((SELECT NULL AS DATA FOR XML RAW, ELEMENTS) as XML).query(‘sum(/row/DATA)’)
Hi pinal,
I think we can use COALESCE like
select SUM(data)
from (select COALESCE(null,0) as data) t
OR
SELECT SUM(isnull(data,0))
FROM (SELECT NULL AS DATA) t
SELECT SUM(CASE WHEN Data IS NULL THEN 0 ELSE DATA END)
FROM (SELECT NULL AS DATA) t
This is same as using isnull or coalesce function