1

I have this query:

    SELECT fksiteID, SUM(SearchTypePerson) + 
       SUM(SearchTypeLocker) + 
       SUM(SearchTypeSpotRandom) + 
       SUM(SearchTypePersVehicle) + 
       SUM(SearchTypeVisitorContractorVehicle) + 
       SUM(SearchTypeCompanyVehicle) + 
       SUM(SearchTypeToilet) + 
       SUM(PatrolExternal) + 
       SUM(PatrolCarPark) + 
       SUM(PatrolPerimeter) + 
       SUM(PatrolInternal) + 
       SUM(VehicleCheckAmbientLine) + 
       SUM(VehicleCheckFridgeLine) + 
       SUM(VehicleCheckSealChecks) + 
       SUM(OtherChecksIDCards) + 
       SUM(OtherChecksIncidentReports) + 
       SUM(OtherChecksColdStoreChecks) AS NumChecks
  FROM [AIP].[dbo].[AAHOfficerDailyActivityReport]
  WHERE MonthOfReport = 6 AND RecordIsDeletedYN = 0 AND fkSiteID in (945,947,948,949,950,951,952)
  GROUP BY fkSiteID

Which gives this result:

fksiteID    NumChecks
945         228
947         27
949         58
951         67
952         1015

However I want it to return:

fksiteID    NumChecks
945         228
947         27
948         0
949         58
950         0
951         67
952         1015

The results from this query will provide the data for a pie chart that will show the breakdown of how many checks were done by each site and I need to show the ones who have no checks done as a zero entry.

This is the raw data of the AAHOfficerDailyActivityReport table:

AAHOfficerDailyActivityReportID fkUserID    fkSiteID    ShiftType   DateOfReport    MonthOfReport   SearchTypePerson    SearchTypeLocker    SearchTypeSpotRandom    SearchTypePersVehicle   SearchTypeVisitorContractorVehicle  SearchTypeCompanyVehicle    SearchTypeToilet    PatrolExternal  PatrolCarPark   PatrolPerimeter PatrolInternal  VehicleCheckAmbientLine VehicleCheckFridgeLine  VehicleCheckSealChecks  OtherChecksIDCards  OtherChecksIncidentReports  OtherChecksColdStoreChecks  RecordIsDeletedYN
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

I looked on Stack Exchange and use of the NULLIF function seems like it may be helpful but I can't get it to work with this query.

Improve this question
0

2 Answers 2

Reset to default
5

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

Improve this answer
0
-2

For this to work, you have to create a query which returns all existing values of 'fksiteID'

Because of request from @paul I tested my own query, and changed it slightly to give correct output.

SELECT X.fksiteID, 
       SUM(NumChecks)
FROM (
    SELECT 
       fksiteID, 
       SUM(SearchTypePerson) + 
       SUM(SearchTypeLocker) + 
       SUM(SearchTypeSpotRandom) + 
       SUM(SearchTypePersVehicle) + 
       SUM(SearchTypeVisitorContractorVehicle) + 
       SUM(SearchTypeCompanyVehicle) + 
       SUM(SearchTypeToilet) + 
       SUM(PatrolExternal) + 
       SUM(PatrolCarPark) + 
       SUM(PatrolPerimeter) + 
       SUM(PatrolInternal) + 
       SUM(VehicleCheckAmbientLine) + 
       SUM(VehicleCheckFridgeLine) + 
       SUM(VehicleCheckSealChecks) + 
       SUM(OtherChecksIDCards) + 
       SUM(OtherChecksIncidentReports) + 
       SUM(OtherChecksColdStoreChecks) AS NumChecks
    FROM [AAHOfficerDailyActivityReport]
    WHERE MonthOfReport = 6 
      AND RecordIsDeletedYN = 0 
      AND fkSiteID in (945,947,948,949,950,951,952)
    GROUP BY fkSiteID

    UNION ALL
    SELECT DISTINCT fksiteID, 0 
    FROM [AAHOfficerDailyActivityReport]
    UNION ALL
    SELECT 950,0 -- special because 950 is not in AAHOfficerDailyActivityReport
     ) AS X
WHERE X.fkSiteID IN (945,947,948,949,950,951,952)
GROUP BY X.fkSiteID

It is also possible to change the SELECT DISTINCT, and add a correct WHERE-clause to it, which makes the WHERE-clause in the end not needed.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.