SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.AAHOfficerDailyActivityReport
(
    ID integer NOT NULL,
    fkUserID integer NOT NULL,
    fkSiteID integer NOT NULL,
    ShiftType varchar(5) NOT NULL,
    DateOfReport datetime2(3) NOT NULL,
    MonthOfReport tinyint NOT NULL,
    SearchTypePerson smallint NOT NULL,
    SearchTypeLocker smallint NOT NULL,
    SearchTypeSpotRandom smallint NOT NULL,
    SearchTypePersVehicle smallint NOT NULL,
    SearchTypeVisitorContractorVehicle smallint NOT NULL,
    SearchTypeCompanyVehicle smallint NOT NULL,
    SearchTypeToilet smallint NOT NULL,
    PatrolExternal smallint NOT NULL,
    PatrolCarPark smallint NOT NULL,
    PatrolPerimeter smallint NOT NULL,
    PatrolInternal smallint NOT NULL,
    VehicleCheckAmbientLine smallint NOT NULL,
    VehicleCheckFridgeLine smallint NOT NULL,
    VehicleCheckSealChecks smallint NOT NULL,
    OtherChecksIDCards smallint NOT NULL,
    OtherChecksIncidentReports smallint NOT NULL,
    OtherChecksColdStoreChecks smallint NOT NULL,
    RecordIsDeletedYN bit NOT NULL
);
INSERT dbo.AAHOfficerDailyActivityReport
    (ID,fkUserID,fkSiteID,ShiftType,DateOfReport,MonthOfReport,SearchTypePerson,SearchTypeLocker,SearchTypeSpotRandom,SearchTypePersVehicle,
    SearchTypeVisitorContractorVehicle,SearchTypeCompanyVehicle,SearchTypeToilet,PatrolExternal,PatrolCarPark,PatrolPerimeter,
    PatrolInternal,VehicleCheckAmbientLine,VehicleCheckFridgeLine,VehicleCheckSealChecks,OtherChecksIDCards,OtherChecksIncidentReports,
    OtherChecksColdStoreChecks,RecordIsDeletedYN)
VALUES
    (1,1,945,'Day','2019-05-18 00:00:00.000',5,3,3,3,3,3,3,3,4,4,4,4,2,2,2,5,5,5,0),
    (2,1,948,'Day','2019-05-17 01:30:00.000',5,3,3,3,3,3,3,3,4,4,4,4,2,2,2,5,5,5,0),
    (3,476,945,'Day','2019-05-20 00:00:00.000',5,8,0,0,0,0,8,0,0,0,0,0,0,0,0,150,2,0,1),
    (4,476,951,'Day','2019-05-31 00:00:00.000',5,0,0,0,0,0,0,0,0,2,0,0,2,0,2,3,0,24,0),
    (5,428,952,'Day','2019-06-01 00:00:00.000',6,3,3,0,0,0,0,3,3,3,3,0,0,0,0,0,0,0,0),
    (6,450,951,'Night','2019-05-31 00:00:00.000',5,1,0,0,1,0,0,0,0,0,0,0,1,0,0,0,0,24,0),
    (7,479,947,'Night','2019-05-31 06:10:06.070',5,0,0,0,7,0,0,0,0,0,0,0,0,0,0,8,0,0,0),
    (8,450,0,'Day','2019-06-01 00:00:00.000',6,1,0,0,0,0,0,0,0,0,0,0,0,0,0,3,0,24,0),
    (9,450,951,'Day','2019-06-02 04:45:00.000',6,1,0,0,0,0,0,0,0,0,0,0,0,0,0,3,0,24,0),
    (10,459,952,'Day','2019-06-02 00:00:00.000',6,0,2,0,0,0,0,2,2,2,2,0,0,0,0,0,0,0,1),
    (11,459,952,'Day','2019-06-02 15:15:00.000',6,56,2,0,0,0,0,2,2,2,2,0,0,0,0,0,0,0,1),
    (12,459,952,'Day','2019-06-02 15:15:00.000',6,56,2,0,0,0,0,2,2,2,2,0,0,0,0,0,0,0,1),
    (13,459,952,'Day','2019-06-02 15:15:00.000',6,0,2,0,0,0,0,2,2,2,2,0,0,0,0,0,0,0,1),
    (14,459,0,'Day','2019-06-02 15:22:43.553',6,0,2,0,0,0,0,2,2,2,2,0,0,0,0,0,0,0,0),
    (15,459,952,'Day','2019-06-02 15:26:00.000',6,0,2,0,0,0,0,2,2,2,2,0,0,0,0,0,0,0,1),
    (16,459,952,'Day','2019-06-03 00:00:00.000',6,120,2,0,0,0,0,3,3,0,0,0,0,0,0,18,0,0,0),
    (17,459,952,'Day','2019-06-03 14:31:00.000',6,120,2,0,0,0,0,3,3,0,0,0,0,0,0,18,0,0,1),
    (18,535,952,'Day','2019-06-03 15:42:12.380',6,55,3,0,0,0,10,3,3,3,3,0,0,0,0,18,0,0,0),
    (19,541,952,'Day','2019-06-03 15:44:34.177',6,55,3,0,0,0,10,3,3,3,3,0,0,0,0,18,0,0,0),
    (20,459,952,'Day','2019-06-03 00:00:00.000',6,120,0,0,0,0,0,0,0,0,0,0,0,0,0,36,0,0,0),
    (21,479,947,'Night','2019-06-02 06:11:00.000',6,0,0,0,0,0,0,0,3,1,0,0,0,0,0,0,0,0,0),
    (22,428,952,'Night','2019-06-04 00:00:00.000',6,2,4,0,0,0,0,4,4,4,4,0,0,0,0,0,0,0,0),
    (23,479,947,'Night','2019-06-03 00:00:00.000',6,0,0,0,7,0,0,0,0,1,0,0,0,0,0,9,0,0,1),
    (24,438,945,'Day','2019-06-04 00:00:00.000',6,7,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0),
    (25,476,947,'Day','2019-06-03 09:27:00.000',6,0,0,0,0,0,10,0,0,0,0,0,0,0,0,4,0,0,1),
    (26,476,947,'Day','2019-06-03 09:27:00.000',6,7,0,0,0,0,10,0,0,0,0,0,0,0,0,4,0,0,1),
    (27,535,952,'Day','2019-06-04 00:00:00.000',6,70,3,0,0,0,10,3,3,3,3,0,0,0,0,18,0,0,0),
    (28,541,952,'Day','2019-06-04 00:00:00.000',6,70,3,0,0,0,10,3,3,3,3,0,0,0,0,18,0,0,0),
    (29,438,945,'Day','2019-06-04 00:00:00.000',6,0,0,0,7,0,33,1,0,0,0,0,0,10,0,16,0,0,0),
    (30,438,945,'Day','2019-06-04 00:00:00.000',6,0,0,0,7,0,33,1,0,0,0,0,0,10,0,16,0,0,0),
    (31,476,947,'Day','2019-06-04 00:00:00.000',6,0,0,0,5,0,0,0,0,2,0,0,0,0,0,1,0,0,0),
    (32,428,952,'Night','2019-06-05 00:00:00.000',6,25,4,0,0,0,0,4,4,4,4,0,0,0,0,0,0,0,0),
    (33,422,945,'Night','2019-06-04 00:00:00.000',6,0,0,0,3,0,2,1,4,4,4,0,0,0,0,4,0,0,0),
    (34,479,947,'Night','2019-06-04 00:00:00.000',6,0,0,0,7,0,0,0,0,1,0,0,0,0,0,5,0,0,1),
    (35,364,949,'Night','2019-06-05 00:00:00.000',6,5,0,0,3,0,0,0,3,3,3,0,0,0,0,20,0,0,0),
    (36,476,947,'Day','2019-06-05 13:52:27.423',6,0,0,0,7,0,0,0,0,2,0,0,0,0,0,6,0,0,0),
    (37,541,952,'Day','2019-06-05 00:00:00.000',6,60,3,0,0,0,10,3,3,3,3,0,0,0,0,18,0,0,0),
    (38,535,952,'Day','2019-06-05 00:00:00.000',6,60,3,0,0,0,10,3,3,3,3,0,0,0,0,18,0,0,0),
    (39,364,949,'Day','2019-06-05 00:00:00.000',6,0,0,0,6,0,0,0,1,1,1,0,0,0,0,12,0,0,0),
    (40,476,951,'Day','2019-06-05 00:00:00.000',6,0,0,0,0,0,0,0,0,0,0,0,3,2,3,7,0,24,0),
    (41,438,945,'Day','2019-06-05 00:00:00.000',6,0,0,0,7,0,30,1,0,0,0,0,0,10,0,16,1,0,0);
41 rows affected

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;


fkSiteID
NumChecks


945
228

947
27

948
0

949
58

950
0

951
67

952
1015