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

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)

One way to solve this problem is by turning the warnings off.

SET ANSI_WARNINGS OFF;
GO

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