Warning Null value is eliminated by an aggregate or other SET operation in Aqua Data Studio

Question

I have a problem when data is null and the warning has appear when the result is display  How to solve this problem   How to change the null data to 0 when no data in the table    This is my code -  SELECT DISTINCT c username             AS assigner officer                  d description          AS ticketcategory                   SELECT Count closed                   FROM   ticket                  WHERE  assigned to   c user id                         AND closed IS NOT NULL                  GROUP  BY assigned to closedcases                   SELECT Count closed                   FROM   ticket                  WHERE  assigned to   c user id                         AND closed IS NULL                  GROUP  BY assigned to opencases FROM   ticket a        JOIN ticketlog b          ON a ticketid   b ticketid        JOIN access c          ON a assigned to   c user id        JOIN ticket category d          ON a cat code   d id        JOIN lookup department e          ON a department code   e code    The result appear like this -   Warnings  --- gt      W  1   Warning  Null value is eliminated by an aggregate or other SET operation             lt ---   assigner officer     ticketcategory     closedcases     opencases      -------------------  -----------------  --------------  ------------   abdulhafiz           Enquiry             null           0              affan                Enquiry            12               null          amirul               Enquiry            1                null          azrul fahmi          Enquiry            45              0              Azwani               Enquiry             null           0              chai                 Enquiry            4                null          dalinawati           Enquiry            1               0              Emmy                 Complaints          null           0              Fadhlia              Enquiry            38              0              fairulhalif          Others             1                null          farikh               Enquiry             null           0              ismailh              Enquiry            28              0              izzahanna            Enquiry             null           0              Kamsuzilawati        Enquiry            1                null

User · Answer

Use ISNULL field  0  It can also be used with aggregates   ISNULL count field   0    However  you might consider changing count field  to count     Edit   try   closedcases   ISNULL      select count closed  from ticket            where assigned to   c user id and closed is not null            group by assigned to   0     opencases   ISNULL       select count closed  from ticket       where assigned to   c user id and closed is null       group by assigned to   0

User · Answer

You want to put the ISNULL inside of the COUNT function  not outside   Not GOOD  ISNULL COUNT field   0   GOOD  COUNT ISNULL field  0

User · Answer

You would mostly be using COUNT to summarize over a UID   Therefore  COUNT  uid   will produce the warning      Warning  Null value is eliminated by an aggregate or other SET operation    whilst being used with a left join  where the counted object does not exist   Using COUNT    in this case would also render incorrect results  as you would then be counting the total number of results  ie parents  that exist   Using COUNT  uid   IS a valid way of counting  and the warning is nothing more than a warning   However if you are concerned  and you want to get a true count of uids in this case then you could use   SUM CASE WHEN  uid  IS NULL THEN 0 ELSE 1 END  AS  new count    This would not add a lot of overheads to your query   tested mssql 2008

User · Answer

One way to solve this problem is by turning the warnings off   SET ANSI WARNINGS OFF  GO

User · Answer

I was getting this error  I just put a WHERE clause for the field which was used within count clause  it solved the issue  Note  if null value exist  check whether its critical for the report  as its excluded in the count   Old query    select city  Count Emp ID  as Emp Count  from Emp DB group by city   New query    select city  Count Emp ID  as Emp Count  from Emp DB where Emp ID is not null group by city

User · Answer

If any Null value exists inside aggregate function you will face this issue   Instead of below code   SELECT Count closed    FROM   ticket   WHERE  assigned to   c user id   AND closed IS NULL   use like   SELECT Count ISNULL closed  0     FROM   ticket   WHERE  assigned to   c user id   AND closed IS NULL

[sql] Warning: Null value is eliminated by an aggregate or other SET operation in Aqua Data Studio

Examples related to sql

Examples related to sql-server-2005