title: Again mysql "count distinct"
body: <p>I have a strange problem with mysql count. When I execute </p>
  
  <pre><code>   SELECT a.inc AS inc,
         a.cls AS cls,
         a.ord AS ord,
         a.fam AS fam,
         a.subfam AS subfam,
         a.gen AS gen,
         aspec AS spec,
         asubspec AS subspec
      FROM a
      WHERE (ainc = 11)
  </code></pre>
  
  <p>I obtain:</p>
  
  <p><img src="https://farm9.staticflickr.com/8034/7946322494_c696399c1c.jpg" alt=""></p>
  
  <p>and that's ok, because I have 2 records.</p>
  
  <p>When I execute </p>
  
  <pre><code> SELECT COUNT(DISTINCT a.inc) AS inc,
         COUNT(DISTINCT a.cls) AS cls,
         COUNT(DISTINCT a.ord) AS ord,
         COUNT(DISTINCT a.fam) AS fam,
         COUNT(DISTINCT asubfam) AS subfam,
         COUNT(DISTINCT a.gen) AS gen,
         COUNT(DISTINCT a.spec) AS spec,
         COUNT(DISTINCT a.subspec) AS subspec
      FROM a
      WHERE (a.inc = 11)
      GROUP BY a.inc
  </code></pre>
  
  <p>I obtain </p>
  
  <p><img src="https://farm9.staticflickr.com/8319/7946322558_0886cbf733.jpg" alt=""></p>
  
  <p>and that's odd because as you see <code>gen, spec and subspec</code> have 0 value on one row.
  I know that <code>count distinct</code> doesn't count zero values. I want to count all value != 0 and after count distinct I want to get </p>
  
  <pre><code>`1 | 2 | 2 | 2 | 2 | 1 | 1 | 1 |`
  </code></pre>
  
  <p>I also try:</p>
  
  <pre><code>SELECT COUNT(DISTINCT a.inc) AS inc,
         SUM(if(a.cls &lt;&gt; 0, 1, 0)) AS cls,
         SUM(if(a.ord &lt;&gt; 0, 1, 0)) AS ord,
         SUM(if(a.fam &lt;&gt; 0, 1, 0)) AS fam,
         SUM(if(a.subfam &lt;&gt; 0, 1, 0)) AS subfam,
         SUM(if(a.gen &lt;&gt; 0, 1, 0)) AS gen,
         SUM(if(a.spec &lt;&gt; 0, 1, 0)) AS spec,
         SUM(if(a.subspec &lt;&gt; 0, 1, 0)) AS subspec
    FROM a
  GROUP BY a.inc
  </code></pre>
  
  <p>and </p>
  
  <pre><code>SELECT COUNT(DISTINCT a.inc) AS inc,
             SUM(DISTINCT if(a.cls &lt;&gt; 0, 1, 0)) AS cls,
             SUM(DISTINCT if(a.ord &lt;&gt; 0, 1, 0)) AS ord,
             SUM(DISTINCT if(a.fam &lt;&gt; 0, 1, 0)) AS fam,
             SUM(DISTINCT if(a.subfam &lt;&gt; 0, 1, 0)) AS subfam,
             SUM(DISTINCT if(a.gen &lt;&gt; 0, 1, 0)) AS gen,
             SUM(DISTINCT if(a.spec &lt;&gt; 0, 1, 0)) AS spec,
             SUM(DISTINCT if(a.subspec &lt;&gt; 0, 1, 0)) AS subspec
  FROM a
  GROUP BY a.inc
  </code></pre>
  
  <p>but it's not working because in first approach doesn't make distinct and sum all duplicate values greater than 0; and in second case it give just 1 and 0 .<br>
  So, can someone help me with that? Thank you in advance. Leo</p>