SQL SERVER – Puzzle Involving NULL – Resolve – Error – Operand data type void type is invalid for sum operator

Today is Monday let us start this week with interesting puzzle. Yesterday I had also posted 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.

SELECT SUM(data)
FROM (SELECT ISNULL(NULL,0) AS DATA) t

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 (http://blog.SQLAuthority.com)

About these ads

34 thoughts on “SQL SERVER – Puzzle Involving NULL – Resolve – Error – Operand data type void type is invalid for sum operator

  1. 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

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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.

  7. 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

  8. 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.

  9. 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

  10. Pingback: SQLAuthority News – Monthly list of Puzzles and Solutions on SQLAuthority.com Journey to SQLAuthority

  11. perfect Alex, by default its taking as char\varchar, 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

  12. 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

  13. 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

  14. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s