title: Trying to sum a count distinct in SQL Server
body: <p>I'm trying to find a count of distinct Id's when a code = "MD".  The table below would give me a count of 2.</p>
  
  <pre><code>    LastName  FirstName  PracId  Code
      Jones     Tom        100     MD
      Jones     Tom        100     MD
      Smith     Robert     200     DO
      Azir      Azul       300     MD
      Azir      Azul       300     MD
  </code></pre>
  
  <p>I've been playing around with something like this:</p>
  
  <pre><code>    Select lastname, firstname, sum(count(distinct pracid) where code = 'MD')) from test group by lastname, firstname
  </code></pre>
  
  <p>I'm getting the "cannot perform an aggregate function on an expression containing an aggregate" error.  I did this before several years ago using a Case statement, but I can't recall how that worked. </p>
  
  <p>Does anyone know how to do this calculation? </p>