How to include 0 results in COUNT()

Question

I have 1 table containing ticket information, and a second with knowledge article information. The tickets can be linked to the appropriate knowledge article.

In the ticket table (IncidentsM1) is a column CBA_KPF_ID which will either contain an article ID or a NULL.

I need to find out how many times each article was linked even if it's 0.

Tables (redacted):

INCIDENTSM1 - Ticket table
- INCIDENT_ID - Ticket ID
- CBA_KPF_ID - Article ID
KMDOCUMENTM1 - Article table
- ID - Article ID

If I use the following code, I get the number of links except for those with 0 links:

SELECT KM1.ID, COUNT(*) AS "Count"
FROM INCIDENTSM1 AS SD LEFT JOIN KMDOCUMENTM1 AS KM1 ON SD.CBA_KPF_ID=KM1.ID
GROUP BY KM1.ID

One of the results is an ID value of NULL with the count of tickets which haven't linked to an article.

If I use the following (from other question) code, I get a list of articles with the count either being 1 or 0 and article IDs appearing in multiple rows:

SELECT SD.CBA_KPF_ID, ISNULL(KM."Count", 0) FROM
(SELECT CBA_KPF_ID FROM INCIDENTSM1) SD
LEFT JOIN
(SELECT ID, count(ID) as "Count" FROM KMDOCUMENTM1 GROUP BY ID) KM
ON SD.CBA_KPF_ID=KM.ID
ORDER BY "Count" DESC

Is it possible to get the list of articles and how many times they were linked even if the number is 0?

EDIT:

I've tried both below answers, but they're either failing to group by ID or still not showing 0 values.

I just tried to generate the list without 0 values, then union a table with all the IDs not in the first statement:

(SELECT KM1.ID, COUNT(*) AS "Count"
FROM INCIDENTSM1 AS SD LEFT JOIN KMDOCUMENTM1 AS KM1 ON SD.CBA_KPF_ID=KM1.ID
GROUP BY KM1.ID)

UNION

SELECT KM.ID, 0
FROM KMDOCUMENTM1 AS KM

WHERE KM.ID NOT IN (
SELECT A.ID FROM
(SELECT KM1.ID, COUNT(*) AS "Count"
FROM INCIDENTSM1 AS SD LEFT JOIN KMDOCUMENTM1 AS KM1 ON SD.CBA_KPF_ID=KM1.ID GROUP BY KM1.ID) A
)
ORDER BY "Count" DESC

Problem here is everything below the UNION seems not to return anything.

score 0 · Answer 1 · answered Nov 18 '16 at 06:48

Let's say you have the following tables:

DECLARE @DataSource01 TABLE
(
    [ID] TINYINT
   ,[RefID] TINYINT
);

DECLARE @DataSource02 TABLE
(
    [RefID] TINYINT
);

INSERT INTO @DataSource01 ([ID], [RefID])
VALUES (1, 1)
      ,(1, 2)
      ,(1, 3)
      ,(2, NULL)
      ,(2, NULL)
      ,(3, 1)
      ,(3, 2)
      ,(3, NULL);

INSERT INTO @DataSource02 ([RefID])
VALUES (1), (2), (3);

The data is:

SELECT *
FROM @DataSource01 DS1
LEFT JOIN @DataSource02 DS2
    ON DS1.[RefID] = DS2.[RefID];

So, I guess you need count of 3 for record 1, count of 0 for record 2, and count of 2 for record 3:

SELECT DS1.[ID]
      ,SUM(IIF(DS2.[RefID] IS NULL, 0, 1))
FROM @DataSource01 DS1
LEFT JOIN @DataSource02 DS2
    ON DS1.[RefID] = DS2.[RefID]
GROUP BY DS1.[ID]

Note, if IIF function is not supported in your SQL Server verstion, you can change it with CASE WHEN expresion.

Tried this, but still not getting any 0 values returned. Just updated the question with another attempt. — Andrei Sevtsenko, Nov 18 '16 at 08:49
Could you show me some sample data (you can use the table I have created) and the expected result? — gotqn, Nov 18 '16 at 09:10

score 0 · Answer 2 · answered Nov 18 '16 at 07:06

0

try this

SELECT KM1.ID,IFF(COUNT(*) is null,0,1) AS "Count"
FROM INCIDENTSM1 AS SD LEFT JOIN KMDOCUMENTM1 AS KM1 ON SD.CBA_KPF_ID=KM1.ID
GROUP BY KM1.ID

answered Nov 18 '16 at 07:06

mansi

837
5
12

The IDs aren't being counted in this query. I'm getting the same number of rows as there are linked articles, but the counter is on 1 for each. – Andrei Sevtsenko Nov 18 '16 at 08:51
I think you mean to say IIF(COUNT(*) is null, 0, COUNT(*)), but that gives me exactly the same results as the first snippet of code in my question. – Andrei Sevtsenko Nov 18 '16 at 08:52

score 0 · Answer 3 · answered Nov 18 '16 at 10:30

if you count the non-null left hand side it should count rows, and if you count the right hand side it should only count matches

SELECT KM1.ID, COUNT(SD.CBA) AS CountAll, COUNT(KM1.ID) AS "CountMatches"
FROM INCIDENTSM1 AS SD LEFT JOIN KMDOCUMENTM1 AS KM1 ON SD.CBA_KPF_ID=KM1.ID
GROUP BY KM1.ID

How to include 0 results in COUNT()

3 Answers3