MS SQL: How to return a count(*) of 0 instead of empty when checking duplicated records

Question

I create an SQL statement to check duplicated records in a table. If the table does not have duplicated records, the count should return zero. The actual result is the count return empty

SELECT COUNT(1) MY_ID_dup_count
FROM mytable
GROUP BY MY_ID
HAVING COUNT(1) >1

Expect Result: MY_ID_dup_count 0

Actual Result: MY_ID_dup_count

MS SQL version: Microsoft SQL Server 2017 - 14.0.3381.3 (X64)

Does this answer your question? [Count Returning blank instead of 0](https://stackoverflow.com/questions/19221630/count-returning-blank-instead-of-0) — Ryan Wilson, Apr 29 '22 at 15:19
Hi Ryan, the "Count Returning blank instead of 0 "solution is similar, but it is not solving my issue. Do you know why the count return empty instead of zero? — Anson, Apr 29 '22 at 15:56

Randy in Marin · Answer 1 · 2022-04-29T15:39:32.940

The return is 1 record for every MY_ID group of 2 or more. You now want to count these if you want the count of MY_ID rather than the duplicate record count from all the groups. This counts both.

SELECT COUNT(*) as [GROUPS_COUNT], SUM(k.[MY_ID_COUNT]) as [RECORDS_COUNT]
FROM (
    SELECT MY_ID, COUNT(*) as [MY_ID_COUNT]
    FROM mytable
    GROUP BY MY_ID
    HAVING COUNT(*) > 1
    ) k

PS Wrap the SUM with ISNULL if you want 0 when there are no records to sum. (Can't remember if this is needed.)

score 0 · Answer 2 · answered Apr 29 '22 at 15:40

0

Something like the following occurs to me:

Count the values without 'having' and then count them with the condition you need

SELECT COUNT(v.MY_ID_dup_count)
FROM(
    SELECT COUNT(1) MY_ID_dup_count
      FROM mytable
     GROUP BY MY_ID
     --HAVING COUNT(1) >1
     )V
 where v.MY_ID_dup_count > 1

answered Apr 29 '22 at 15:40

Marvin R. Pinto Z.

1
2

Hi Marvin, Your solution is good. Count returns '0'. I wonder if we have a solution without a sub-query? – Anson Apr 29 '22 at 15:51

score 0 · Answer 3 · answered Apr 29 '22 at 15:44

You are talking about post-processing the result of your duplicate-check. You could do it this way:

DROP TABLE IF EXISTS #Duplicates;
SELECT MY_ID_dup_count = COUNT(1)
    INTO #Duplicates
    FROM mytable
    GROUP BY MY_ID
    HAVING COUNT(1) > 1;
IF @@ROWCOUNT = 0
    SELECT MY_ID_dup_count = 0;
ELSE
    SELECT * FROM #Duplicates;

MS SQL: How to return a count(*) of 0 instead of empty when checking duplicated records

3 Answers3