Post reply

Warning: Null value is eliminated by an aggregate

  • May 14, 2004 at 8:15 am

    #87152

    Let's suppose I have a table with the following structure and data:

    CREATE TABLE tbl (
 x int,
 y int
)
    --CREATE INDEX X ON TBL(X)
    INSERT INTO tbl VALUES (1, NULL)
INSERT INTO tbl VALUES (1, 10)
INSERT INTO tbl VALUES (2, 20)
INSERT INTO tbl VALUES (2, 30)
INSERT INTO tbl VALUES (3, NULL)
INSERT INTO tbl VALUES (4, 0)

    I want to obtain the following result:

    X           SumOfY      
----------- ----------- 
1           10
2           50
3           NULL
4           0
    (4 row(s) affected)

    I can get this with the following (obvious) query:

    SELECT X, SUM(Y) AS SumOfY FROM tbl GROUP BY X

    But this also gives me the following annoying warning (which bothers me when I look at the Connection.Errors collection in ADO):

    "Warning: Null value is eliminated by an aggregate or other SET operation."

    How can I get the exact result specified above, without the warning ?

    The first things I've tried are:

    SELECT X, SUM(ISNULL(Y,0)) AS SumOfY FROM tbl GROUP BY X
    SELECT X, NULLIF(SUM(ISNULL(Y,0)),0) AS SumOfY FROM tbl GROUP BY X

    But neither gives me exactly desired result. After that I've tried:

    SELECT X, CASE WHEN EXISTS (SELECT * FROM tbl a WHERE a.X=b.X and a.Y IS NOT NULL)
THEN SUM(ISNULL(Y,0)) ELSE NULL END AS SumOfY FROM tbl b GROUP BY X

    But this requires two table scans (or an index scan and an index seek) and it's too complicated.

    Is there any simple way to get the desired result without the warning ?

    Razvan Socol

  • May 14, 2004 at 9:41 am

    #506511

    See the SET ANSI_WARNINGS item in SQL BOL

  • May 14, 2004 at 1:15 pm

    #506533

    Here's the issue:

    NULL is unknown, it has no value. When you add something and NULL together the SUM is NULL. So 1 + 2 + NULL does NOT equal 3, it is NULL. So the warning is letting you know that the column you are SUMming has one or more NULLs that are being ignored.

    As Antares says, turn off the ANSI Warnings and you won't see that warning.

    -SQLBill

    Remember: NULL is the same as "I don't know"

  • July 6, 2004 at 3:13 am

    #513416

    I don't want to use "SET ANSI_WARNINGS OFF" because it is required to be ON to use indexes on computed columns or indexed views.

    But I found a solution that is a little bit less complicated than my previous one:

    SELECT X, CASE WHEN SUM(CASE WHEN Y IS NULL THEN 0 ELSE 1 END)=0
THEN NULL ELSE SUM(ISNULL(Y,0)) END AS SumOfY FROM tbl GROUP BY X

    It's still a long query, but it takes only one table scan (or index scan).

    Razvan

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply