title: MySQL - Conditional COUNT with GROUP BY
body: <p>I'm trying to refine a query that I am currently using:</p>
  
  <pre><code>SELECT `puid`,
         COUNT(DISTINCT `droid_v`) AS DROID,
         COUNT(DISTINCT `sig_v`) AS sig,
         SUM(NoExt)  AS hits
         FROM temp
         GROUP BY `puid`
  </code></pre>
  
  <p>And I need to get it to only count <code>droid_v</code> where <code>droid_V</code> is greater than 0. Is it possible to condition the count in this way? At the moment, it is counting zero values as countable, and I can't really change the zeros to <code>null</code> values.  </p>
  
  <p>I do not need to know the count value for <code>droid_V</code> = 0, I only need to count it if it has a number greater than 0. That number will always be either 0, 1, 2, 3, or 4. </p>
  
  <hr>
  
  <p>I have tried:</p>
  
  <pre><code>SELECT `puid`,
     COUNT(DISTINCT CASE WHEN `droid_v` &gt; 0 THEN 1 END) AS DROID,
     COUNT(DISTINCT `sig_v`) AS sig,
     SUM(`NoExt`)  AS hits
     FROM temp
     GROUP BY `puid`
  </code></pre>
  
  <p>But this gives a binary result (either 0 or 1) not the count I am expecting. </p>
  
  <p>example output = </p>
  
  <pre><code>puid            DROID   sig     hits
  No PUID         1       1        252
  x-fmt/92        1       5       1008
  </code></pre>
  
  <p>anticipated output:</p>
  
  <pre><code>puid            DROID   sig     hits
  No PUID         1       1        252
  x-fmt/92        3       5       1008
  </code></pre>
  
  <p>Sample data:</p>
  
  <pre><code>id;puid;droid_v;sig_v;speed;Ext;NoExt;tally
  
  
  1;"No PUID";"3";"v13";"SLOW";"0";"63";"63"
  2;"x-fmt/92";"3";"v13";"SLOW";"63";"0";"63"
  3;"x-fmt/92";"3";"v37";"SLOW";"63";"63";"126"
  4;"x-fmt/92";"3";"v45";"SLOW";"63";"63";"126"
  5;"x-fmt/92";"3";"v49";"SLOW";"63";"63";"126"
  6;"x-fmt/92";"3";"v50";"SLOW";"63";"63";"126" 
  7;"x-fmt/92";"5";"v13";"SLOW";"63";"0";"63"
  8;"No PUID";"5";"v13";"SLOW";"0";"63";"63"
  9;"x-fmt/92";"5";"v37";"SLOW";"63";"63";"126"
  10;"x-fmt/92";"5";"v45";"SLOW";"63";"63";"126"
  11;"x-fmt/92";"5";"v49";"SLOW";"63";"63";"126"
  12;"x-fmt/92";"5";"v50";"SLOW";"63";"63";"126"
  13;"No PUID";"6";"v13";"FAST";"0";"63";"63"
  14;"x-fmt/92";"6";"v13";"SLOW";"63";"0";"63"
  15;"No PUID";"6";"v13";"SLOW";"0";"63";"63"
  16;"x-fmt/92";"6";"v13";"FAST";"63";"0";"63"
  17;"x-fmt/92";"6";"v37";"SLOW";"63";"63";"126"
  18;"x-fmt/92";"6";"v37";"FAST";"63";"63";"126"
  19;"x-fmt/92";"6";"v45";"FAST";"63";"63";"126"
  20;"x-fmt/92";"6";"v45";"SLOW";"63";"63";"126"
  21;"x-fmt/92";"6";"v49";"FAST";"63";"63";"126"
  22;"x-fmt/92";"6";"v49";"SLOW";"63";"63";"126"
  23;"x-fmt/92";"6";"v50";"FAST";"63";"63";"126"
  24;"x-fmt/92";"6";"v50";"SLOW";"63";"63";"126"
  </code></pre>