Return to Revisions

###Option 1

Using the deprecated GROUP BY ALL syntax:

SELECT 
    AODAR.fkSiteID,
    NumChecks =
        ISNULL
        (
            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)
            , 0
        )
FROM dbo.AAHOfficerDailyActivityReport AS AODAR
WHERE 
    AODAR.MonthOfReport = 6 
    AND AODAR.RecordIsDeletedYN = 0
GROUP BY ALL 
    AODAR.fkSiteID
HAVING 
    AODAR.fkSiteID IN (945,947,948,949,950,951,952)
ORDER BY
    AODAR.fkSiteID;

###Option 2

Or equivalently, without the deprecated syntax:

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
(
    SELECT
        AODAR.fkSiteID,
        AODAR.SearchTypePerson,
        AODAR.SearchTypeLocker,
        AODAR.SearchTypeSpotRandom,
        AODAR.SearchTypePersVehicle,
        AODAR.SearchTypeVisitorContractorVehicle,
        AODAR.SearchTypeCompanyVehicle,
        AODAR.SearchTypeToilet,
        AODAR.PatrolExternal,
        AODAR.PatrolCarPark,
        AODAR.PatrolPerimeter,
        AODAR.PatrolInternal,
        AODAR.VehicleCheckAmbientLine,
        AODAR.VehicleCheckFridgeLine,
        AODAR.VehicleCheckSealChecks,
        AODAR.OtherChecksIDCards,
        AODAR.OtherChecksIncidentReports,
        AODAR.OtherChecksColdStoreChecks
    FROM dbo.AAHOfficerDailyActivityReport AS AODAR
    WHERE 
        AODAR.MonthOfReport = 6 
        AND AODAR.RecordIsDeletedYN = 0
        AND AODAR.fkSiteID IN (945,947,948,949,950,951,952)

    UNION ALL

    SELECT
        AODAR.fkSiteID,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
    FROM dbo.AAHOfficerDailyActivityReport AS AODAR
    WHERE 
        AODAR.fkSiteID IN (945,947,948,949,950,951,952)
) AS AODAR
GROUP BY
    AODAR.fkSiteID
ORDER BY
    AODAR.fkSiteID;

Results:

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

db<>fiddle demo

###OPtion 3

Or, if you really intended 950 to be in the results even though it does not appear in the data set:

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