Again mysql "count distinct"

Question

I have a strange problem with mysql count. When I execute

   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)

I obtain:

and that's ok, because I have 2 records.

When I execute

 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

I obtain

and that's odd because as you see gen, spec and subspec have 0 value on one row. I know that count distinct doesn't count zero values. I want to count all value != 0 and after count distinct I want to get

`1 | 2 | 2 | 2 | 2 | 1 | 1 | 1 |`

I also try:

SELECT COUNT(DISTINCT a.inc) AS inc,
       SUM(if(a.cls <> 0, 1, 0)) AS cls,
       SUM(if(a.ord <> 0, 1, 0)) AS ord,
       SUM(if(a.fam <> 0, 1, 0)) AS fam,
       SUM(if(a.subfam <> 0, 1, 0)) AS subfam,
       SUM(if(a.gen <> 0, 1, 0)) AS gen,
       SUM(if(a.spec <> 0, 1, 0)) AS spec,
       SUM(if(a.subspec <> 0, 1, 0)) AS subspec
  FROM a
GROUP BY a.inc

and

SELECT COUNT(DISTINCT a.inc) AS inc,
           SUM(DISTINCT if(a.cls <> 0, 1, 0)) AS cls,
           SUM(DISTINCT if(a.ord <> 0, 1, 0)) AS ord,
           SUM(DISTINCT if(a.fam <> 0, 1, 0)) AS fam,
           SUM(DISTINCT if(a.subfam <> 0, 1, 0)) AS subfam,
           SUM(DISTINCT if(a.gen <> 0, 1, 0)) AS gen,
           SUM(DISTINCT if(a.spec <> 0, 1, 0)) AS spec,
           SUM(DISTINCT if(a.subspec <> 0, 1, 0)) AS subspec
FROM a
GROUP BY a.inc

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 .
So, can someone help me with that? Thank you in advance. Leo

What makes you think DISTINCT doesn't count 0 values? It doesn't count NULL values, but NULL is not the same as 0. — Barmar
– Barmar, Commented Sep 7, 2012 at 0:17

Ike Walker · Accepted Answer · 2012-09-07 00:20:58Z

I know that count distinct doesn't count zero values.

I don't know where you got that idea, but it is not correct. Perhaps you are thinking of NULL values? One way to get the results you desire is to treat the 0s as NULL in your distinct count.

Try something like this (I also removed the group by, which was not helping):

SELECT COUNT(DISTINCT case when a.inc = 0 then null else a.inc end) AS inc,
       COUNT(DISTINCT case when a.cls = 0 then null else a.cls end) AS cls,
       COUNT(DISTINCT case when a.ord = 0 then null else a.ord end) AS ord,
       COUNT(DISTINCT case when a.fam = 0 then null else a.fam end) AS fam,
       COUNT(DISTINCT case when a.subfam = 0 then null else a.subfam end) AS subfam,
       COUNT(DISTINCT case when a.gen = 0 then null else a.gen end) AS gen,
       COUNT(DISTINCT case when a.spec = 0 then null else a.spec end) AS spec,
       COUNT(DISTINCT case when a.subspec = 0 then null else a.subspec end) AS subspec
    FROM a
    WHERE (a.inc = 11)

Barmar · Accepted Answer · 2012-09-07 00:22:08Z

2

SELECT COUNT(DISTINCT a.inc) AS inc,
       COUNT(DISTINCT NULLIF(a.cls, 0)) AS cls,
       COUNT(DISTINCT NULLIF(a.ord, 0)) AS ord,
       COUNT(DISTINCT NULLIF(a.fam, 0)) AS fam,
       COUNT(DISTINCT NULLIF(a.subfam, 0)) AS subfam,
       COUNT(DISTINCT NULLIF(a.gen, 0)) AS gen,
       COUNT(DISTINCT NULLIF(a.spec, 0)) AS spec,
       COUNT(DISTINCT NULLIF(a.subspec, 0)) AS subspec
FROM a

answered Sep 7, 2012 at 0:22

Barmar

789k57 gold badges555 silver badges669 bronze badges

2 Comments

Barmar Over a year ago

NULLIF is a MySQL function equivalent to the CASE construct in Ike Walter's answer.

Leonard Lepadatu Over a year ago

My God this it's quite elegant! Thank you! :)

Collectives™ on Stack Overflow

Again mysql "count distinct"

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related