Re: How to find number of non-null fields in each record?

[Chris Stubben]

Hi,

You probably need to use a CASE statement (or IF in mysql, but its non-standard SQL). just add columns S1-S5 and change nulls to 0 and non-nulls to 1.

select (if(S1 is null, 0,1)+ if(S2 is null,0,1)+ if(S3 is null,0,1)+ if(S4 is null,0,1)+ if(S5 is null,0,1)) as fill_count, count(*) as frequency from tmp group by 1;
+------------+-----------+
| fill_count | frequency |
+------------+-----------+
| 1 | 2 |
| 2 | 2 |
| 5 | 1 |
+------------+-----------+


Chris