Return to Revisions

The following code produces the results you want by outer joining the list of sites that must appear in the output, with the results of your data query grouped by site id.

SELECT
    Sites.fkSiteID,
    NumChecks = ISNULL(Totals.NumChecks, 0)
FROM (VALUES (945),(947),(948),(949),(950),(951),(952)) AS Sites (fkSiteID)
LEFT JOIN
(
    SELECT
        AODAR.fkSiteID,
        NumChecks =
            SUM(AODAR.SearchTypePerson) + 
            SUM(AODAR.SearchTypeLocker) + 
            SUM(AODAR.SearchTypeSpotRandom) + 
            SUM(AODAR.SearchTypePersVehicle) + 
            SUM(AODAR.SearchTypeVisitorContractorVehicle) + 
            SUM(AODAR.SearchTypeCompanyVehicle) + 
            SUM(AODAR.SearchTypeToilet) + 
            SUM(AODAR.PatrolExternal) + 
            SUM(AODAR.PatrolCarPark) + 
            SUM(AODAR.PatrolPerimeter) + 
            SUM(AODAR.PatrolInternal) + 
            SUM(AODAR.VehicleCheckAmbientLine) + 
            SUM(AODAR.VehicleCheckFridgeLine) + 
            SUM(AODAR.VehicleCheckSealChecks) + 
            SUM(AODAR.OtherChecksIDCards) + 
            SUM(AODAR.OtherChecksIncidentReports) + 
            SUM(AODAR.OtherChecksColdStoreChecks)
    FROM dbo.AAHOfficerDailyActivityReport AS AODAR
    WHERE
        AODAR.MonthOfReport = 6 
        AND AODAR.RecordIsDeletedYN = 0
    GROUP BY 
        AODAR.fkSiteID
) AS Totals
    ON Totals.fkSiteID = Sites.fkSiteID
ORDER BY
    Sites.fkSiteID;

Results:

╔══════════╦═══════════╗
║ fkSiteID ║ NumChecks ║
╠══════════╬═══════════╣
║      945 ║       228 ║
║      947 ║        27 ║
║      948 ║         0 ║
║      949 ║        58 ║
║      950 ║         0 ║
║      951 ║        67 ║
║      952 ║      1015 ║
╚══════════╩═══════════╝

db<>fiddle demo