Distinct of two columns grouping on another column

Question

I am trying to get a count of number of duplicate values on two columns grouping on another column in SQL Server.

Below is a sample scenario I am working on.

    DECLARE @mytable TABLE (CampName varchar(10),ID VARCHAR(10),ListName varchar(10))
    INSERT INTO @mytable
            ( CampName, ID, ListName )
    VALUES  ( 'A',   'X',   'Y' ), ( 'A',   'X',   'Y' ), 
            ( 'A',   'Y',   'Z' ), ( 'A',   'Y',   'Z' ),
            ( 'A',   'Y',   'Z' ), ( 'A',   'P',   'Q' ),
            ( 'B',   'X',   'Y' ), ( 'B',   'X',   'Y' ), 
            ( 'B',   'Y',   'Z' ), ( 'B',   'Y',   'Z' ),
            ( 'B',   'Y',   'Z' ), ( 'B',   'P',   'Q' ),
            ( 'B',   'R',   'S' ), ( 'B',   'R',   'S' )

This would result in the following table.

 CampName   ID  ListName
-------------------------------------
      A     X     Y
      A     X     Y -- Duplicate Record
      A     Y     Z
      A     Y     Z -- Duplicate Record
      A     Y     Z -- Duplicate Record
      A     P     Q
      B     X     Y 
      B     X     Y -- Duplicate Record
      B     Y     Z
      B     Y     Z -- Duplicate Record
      B     Y     Z -- Duplicate Record
      B     P     Q
      B     R     S
      B     R     S -- Duplicate Record

I need the output as follows:

CampName   dupcount
-------------------
A            3
B            4

Basically, I need to figure out the number of duplicate (ID,ListName) for each CampName irrespective of what the duplicate values are.

Let me know if I can clarify something else in this regard. Any help would be greatly appreciated.

Answer 1

You can use the following query:

SELECT CampName, SUM(cnt) AS dupcount
FROM (
  SELECT CampName, COUNT(*) - 1 AS cnt
  FROM @mytable
  GROUP BY CampName, ID, ListName
  HAVING COUNT(*) > 1) AS t
GROUP BY CampName

The inner query uses a HAVING clause to filter out non-duplicate entries. It also calculates the number of duplicate records per ID, ListName. The outer query simply sums the number of duplicates.

Answer 2

I believe that the distinct number of combinations of ID and ListName need to be subtracted from the total count for each CampName group to get the correct result.

SELECT t.CampName,
       COUNT(*) - COUNT(DISTINCT 'ColOne' + ID + 'ColTwo' + ListName) AS dupcount
FROM yourTable t
GROUP BY CampName

This query employs a trick, which is concatenating the ID and ListName columns, which are both text, to effectively form a pseudo-group. The need for this is that DISTINCT only works on a single column, but you have two columns which need to be considered.

Reference: Quora: In SQL, how to I count DISTINCT over multiple columns?