How to count specific values in a table.

Question

I've a column that have 15 distinct values. I'd like to count how many there are of a few of them, I've come up with e.g.

select a,COUNT(IFNULL(b != 1,NULL)),COUNT(IFNULL(b != 2,NULL)) from 
  mytable group by a

select a,SUM(CASE WHEN a = 1 THEN 1 ELSE 0)),SUM(CASE WHEN a = 2 THEN 1 ELSE 0))  from 
  mytable group by a

What's the best way of doing this ? (note, I need to pivot those values to columns, a simple select a,b,count(*) from mytable where b=1 or b=2 group by a,b; won't do.)

Answer 1

Hi,

First of all you misunderstood the IFNULL function (you probably wanted IF). See the documentation http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html .

The second query you have in your question will give you what you want. But SUM(a=x) is more than sufficient. In MySQL true is equal to 1 and false is equal to 0.

Answer 2

have u try cross join?

select *
  from (
        select a, sum(...) as aSum 
          from mytable 
         where a... 
         group 
             by a
       ) as forA
       cross join (
                   select b, sum(...) as bsum 
                     from (
                           select * 
                             from mytable 
                            where b... 
                            group 
                               by b
                          )
                  ) as forB;

Answer 3

Of the two methods suggested in the question, I recommend the second:

select a,
       SUM(CASE WHEN b = 1 THEN 1 ELSE 0) b1,
       SUM(CASE WHEN b = 2 THEN 1 ELSE 0) b2
from mytable 
group by a

- as it is both simpler and (I think) easier to understand, and therefore to maintain. I recommend including column aliases, as they make the output easier to understand.