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.

Paul White ♦
95.4k30 gold badges440 silver badges689 bronze badges
asked Jun 13, 2019 at 15:47
0

2 Answers 2

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

answered Jun 13, 2019 at 21:45
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.

answered Jun 13, 2019 at 17:10
0

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.