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

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)

sql-servercountduplicates


Solution 1:[1]

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.)

Solution 2:[2]

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

Solution 3:[3]

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;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Marvin R. Pinto Z.
Solution 3 Nick Allan

Related Questions