title: Distinct of two columns grouping on another column
body: <p>I am trying to get a count of number of duplicate values on two columns grouping on another column in SQL Server.</p>
  
  <p>Below is a sample scenario I am working on.</p>
  
  <pre><code>    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' )
  </code></pre>
  
  <p>This would result in the following table.</p>
  
  <pre><code> 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
  </code></pre>
  
  <p>I need the output as follows:</p>
  
  <pre><code>CampName   dupcount
  -------------------
  A            3
  B            4
  </code></pre>
  
  <p>Basically, I need to figure out the number of duplicate (ID,ListName) for each CampName irrespective of what the duplicate values are.</p>
  
  <p>Let me know if I can clarify something else in this regard.
  Any help would be greatly appreciated.</p>