mysql error “ Operand should contain 1 column(s)” when count occurrences in multiple columns

I want to get occurrences in multiple columns and count null values as zeros.

I have tried this.

select IFNULL((select  id, count(*) as v
from    (
        select id, s1 as val from t1
        union all select id, s2 from t1
        union all select id, s3 from t1
        union all select id, s4 from t1
        union all select id, s5 from t1
        ) sub
where   val = 3
group by id),0) as Valcount;

but it returns error “Operand should contain 1 column(s)”

Answer

I think you want:

select id, sum(val = 3) as valcount
from (select id, s1 as val from t1 union all
      select id, s2 from t1 union all
      select id, s3 from t1 union all
      select id, s4 from t1 union all
      select id, s5 from t1
     ) sub
group by id;

That is, you don’t want to “count NULL values as 0s” (whatever that means. You want to include ids that have no 3 in the result set with a 0 count.

I should note that forpas’s answer on your previous question is the right way to solve this problem. There is no need to aggregate over the entire dataset — that can be an expensive operation.