Making a group by with count(*) but return 0 if no joins are made

user_1406168 0
Viewed 32

I have following SQL:

    select  
    s.Id,
    s.SPOSiteId,
    s.Url,  
    COUNT(*) as ItemCountScan,
    s.ItemCount as ItemCountList,    
    SUM(f.CountUniqueRoleAssignments) as SharedWithCount,
    SUM(s.MemberCount) as MembersCount,
    SUM(s.OwnerCount) as OwnersCount,       
    s.Created,
    s.LastItemUserModifiedDate,
    s.Template,
    s.TimeZoneId,
    s.Language    
    
from sites s
LEFT JOIN Files f ON f.SiteId = s.Id
GROUP BY 
    s.Id,
    s.SPOSiteId,
    s.Url,
    s.ItemCount,
    s.Forvaltning,
    s.KoncernEnhed,
    s.Enhed,
    s.KKOrg,
    s.BestiltAf,
    s.DataAnsvarlig,
    s.DirekteChef,
    s.Created,
    s.LastItemUserModifiedDate,
    s.Template,
    s.TimeZoneId,
    s.Language,
    s.GroupSettings,
    s.ExternalAccess,
    s.Inserted

As you can see it joins two tables - files and sites. Everything works as expected unless this in the select:

COUNT(*) as ItemCountScan

The column should tell how many files are there on the given site. If the site has files it works as expected. However if the site does not have any files then it returns 1 (because the site count it self in the group by). Is there any way to make this work both for sites with no files and sites with files?

sql-server sql
2 Answers

You want to count something from the second table, such as:

COUNT(f.SiteId) as ItemCountScan
user_11440350

One option is to put the count inside the join.

    select  
    s.Id,
    s.SPOSiteId,
    s.Url,  
    f.ItemCountScan,
    s.ItemCount as ItemCountList,    
    f.SharedWithCount,
    SUM(s.MemberCount) as MembersCount,
    SUM(s.OwnerCount) as OwnersCount,       
    s.Created,
    s.LastItemUserModifiedDate,
    s.Template,
    s.TimeZoneId,
    s.Language    
    
from sites s
LEFT JOIN (
    SELECT SiteId,
        COUNT(*) as ItemCountScan,
        SUM(f.CountUniqueRoleAssignments) as SharedWithCount
    FROM Files
    GROUP BY SiteId
) f ON f.SiteId = s.Id
GROUP BY 
    s.Id,
    s.SPOSiteId,
    s.Url,
    s.ItemCount,
    s.Forvaltning,
    s.KoncernEnhed,
    s.Enhed,
    s.KKOrg,
    s.BestiltAf,
    s.DataAnsvarlig,
    s.DirekteChef,
    s.Created,
    s.LastItemUserModifiedDate,
    s.Template,
    s.TimeZoneId,
    s.Language,
    s.GroupSettings,
    s.ExternalAccess,
    s.Inserted,
    f.ItemCountScan,
    f.SharedWithCount;

This can also be done as a correlated APPLY, note that the optimizer is usually able to transform between these two syntaxes and choose the most efficient.

    select  
    s.Id,
    s.SPOSiteId,
    s.Url,  
    f.ItemCountScan,
    s.ItemCount as ItemCountList,    
    f.SharedWithCount,
    SUM(s.MemberCount) as MembersCount,
    SUM(s.OwnerCount) as OwnersCount,       
    s.Created,
    s.LastItemUserModifiedDate,
    s.Template,
    s.TimeZoneId,
    s.Language    
    
from sites s
CROSS APPLY (     -- or OUTER if you want nulls
    SELECT
        COUNT(*) as ItemCountScan,
        SUM(f.CountUniqueRoleAssignments) as SharedWithCount
    FROM Files
    WHERE f.SiteId = s.Id
    -- GROUP BY ()    -- if you want nulls
) f
GROUP BY 
    s.Id,
    s.SPOSiteId,
    s.Url,
    s.ItemCount,
    s.Forvaltning,
    s.KoncernEnhed,
    s.Enhed,
    s.KKOrg,
    s.BestiltAf,
    s.DataAnsvarlig,
    s.DirekteChef,
    s.Created,
    s.LastItemUserModifiedDate,
    s.Template,
    s.TimeZoneId,
    s.Language,
    s.GroupSettings,
    s.ExternalAccess,
    s.Inserted,
    f.ItemCountScan,
    f.SharedWithCount;

This returns 0 as it is a scalar aggregate (no GROUP BY). If you want NULL instead of 0, you can switch to OUTER APPLY and place GROUP BY () inside the apply.

user_148689970
Related