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(data)
FROM (SELECT COALESCE(NULL,0) AS DATA) t
David Ames is correct, and your “solution” just hard-codes the data, rather than the function, so I don’t like that. This “puzzle” does, however, expose a bug (or, at least, an anomaly) in SQL Server, where aggregate functions, other than COUNT, supposedly ignore NULLs. If that’s correct (and it’s in BOL), then his example:
SELECT SUM(data)
FROM (SELECT CAST(NULL AS int) data) AS t;
should evaluate to 0 and not NULL.
SELECT SUM(data)
FROM (SELECT convert(int,NULL) AS DATA) t
Its COALESCE!!
SELECT MAX(data)
FROM (SELECT COALESCE(NULL,0) AS DATA) t
or
SELECT SUM(data)
FROM (SELECT COALESCE(NULL,0) AS DATA) t
SELECT SUM(data)
FROM (SELECT isnumeric(null) AS DATA) t
SELECT SUM(data)
FROM (SELECT coalesce(NULL,0) AS DATA) t
perfect Alex, by default its taking as charvarchar, so we’re getting error. by converting in to numeric datatype, will resolve the error.
SELECT SUM(data)
FROM (SELECT convert(varchar,NULL) AS DATA) t
Go
SELECT SUM(data)
FROM (SELECT convert(numeric ,NULL) AS DATA) t
Use This
Select Case When Sum(Data) is Null then 0 else Sum(Data) end as Data from t.
it will gve u exact data and not throw any typem of error.Try this.
SELECT SUM(isnull(DATA,0))
FROM (SELECT NULL AS DATA) t
SELECT SUM(COALESCE(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
let I have a table name x which have two cloumn date1 as nvarchar(10) and date2 as (varchar(30)).the format of storing data is like this date2=Aug 6 2010 3:32PM and date1=12112010;
But not I wane to select the difference between these two dates.
So please reply quickly…..It is an urgent
let I have a table name x which have two cloumn date1 as nvarchar(10) and date2 as (varchar(30)).the format of storing data is like this date2=Aug 6 2010 3:32PM and date1=12112010;
But now I want to select the difference between these two dates.
So please reply quickly…..It is an urgent
SELECT SUM(data)
FROM (SELECT ” AS DATA) t