Warning: Null is excluded by an aggregate or other SET operation in Aqua Data Studio

Question

I have a problem when the data is zero, and a warning appears when the result is displayed. How to solve this problem?. How to change null data to 0 when there is 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 is as follows: -

  Warnings: ---> W (1): Warning: Null value is eliminated by an aggregate or other SET operation. <--- 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) 

Answer 1

Basically you would use COUNT to sum by UID. therefore

COUNT([uid]) will give a warning:

Warning: Null is excluded by aggregation or other SET operation.

when used with a left join where the counted object does not exist.

Using COUNT(*) in this case will also lead to incorrect results, since you would have to calculate the total number of results received (i.e. parents).

Using COUNT([uid]) is a valid way of counting, and a warning is nothing more than a warning. However, if you are interested, and you want to get the true number of uids in this case, you can use:

 SUM(CASE WHEN [uid] IS NULL THEN 0 ELSE 1 END) AS [new_count] 

This would not add a lot of overhead to your request. (checked by mssql 2008)

Answer 2

One way to solve this problem is to turn off alerts.

 SET ANSI_WARNINGS OFF; GO 

Answer 3

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:

to 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), 

Answer 4

You want to put ISNULL inside the COUNT function, and not outside:

Not GOOD: ISNULL(COUNT(field), 0)

GOOD: COUNT(ISNULL(field, 0))

Answer 5

If any Null value exists inside the aggregate function, you will run into this problem. Instead of the code below

  SELECT Count(closed) FROM ticket WHERE assigned_to = c.user_id AND closed IS NULL 

use as

 SELECT Count(ISNULL(closed, 0)) FROM ticket WHERE assigned_to = c.user_id AND closed IS NULL