CODEWITHSUNDEEP
sqlsql-server
Thomas Segato
Thomas Segato

Reputation: 5257

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

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?

Upvotes: 2

Views: 58

Answers (2)

Charlieface
Charlieface

Reputation: 72153

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

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

COUNT(f.SiteId) as ItemCountScan

Upvotes: 1

Related Questions