Return a 0 when no data present

Question

I have looked at a few questions similar and tried to apply my own knowledge to get the query to work correctly but still cant get the outcome i desire. I have 5 Call lines i need to report on from our DB. The problem is some of them can go a full day without a call. Instead of reporting 0, it just doesn't pull the line through. Unfortunately for Reporting purposes I need it to put 0.

SELECT CASE [Campaign]
WHEN '1' THEN 'Line A'
WHEN '2' THEN 'Line B'
WHEN '3' THEN 'Line C'
WHEN '4' THEN 'Line D'
WHEN '5' THEN 'Line E'
ELSE 'Outbound Dialling' END AS [Line_name],
COUNT(UniqueID) AS [Total Calls]
FROM Telephone_Table
WHERE [CallTime] BETWEEN '2017-12-01 00:00:00' AND '2017-12-01 23:59:59'
AND [Campaign] IN ('402','403','404','405','406')

Result:

Line_name   | Campaign  |  Total Calls
Line A      |  402      |       13
Line B      |  403      |       11
Line C      |  405      |       26
Line E      |  406      |       178

Quite often Line D doesn't get a call. Instead of listing it as 0 it will miss it completely.

I have tried the following but none have worked: COUNT(UniqueID)+1

CASE WHEN COUNT(UniqueID) = '0' THEN 1 ELSE COUNT(UniqueID)

I tried creating another table in the DB with just these lines and a name in there to join against instead of using a "Case When" in case that was the issue but that didn't work.

Any one have any suggestions? Thank you.

Answer 1

Here's a suggestion: add a dummy for each group you want displayed, then subtract 1 from the count:

SELECT CASE [Campaign]
    WHEN '402' THEN 'Line A'
    WHEN '403' THEN 'Line B'
    WHEN '404' THEN 'Line C'
    WHEN '405' THEN 'Line D'
    WHEN '406' THEN 'Line E'
    ELSE 'Outbound Dialling' END AS [Line_name]
    ,[Campaign]
    ,COUNT(UniqueID)-1 AS [Total Calls]
FROM 
    (SELECT [Campaign], [UniqueID] FROM Telephone_Table
    WHERE [CallTime] BETWEEN '2017-12-01 00:00:00' AND '2017-12-01 23:59:59'
        AND [Campaign] IN ('402','403','404','405','406')
    UNION SELECT '402' AS [Campaign], 'DUMMY1' AS UniqueID
    UNION SELECT '403' AS [Campaign], 'DUMMY2' AS UniqueID
    UNION SELECT '404' AS [Campaign], 'DUMMY3' AS UniqueID
    UNION SELECT '405' AS [Campaign], 'DUMMY4' AS UniqueID
    UNION SELECT '406' AS [Campaign], 'DUMMY5' AS UniqueID) AS a
GROUP BY [Campaign]

I may have mixed up my syntax, I don't have sql-server handy at the moment to check, but this general idea should work.