MySQL COUNT NULL contents by column, group by columns

Question

How to count nonzero entries by field / column? I see some answers for counting rows, but I can't hack how to do this for columns.

Input:

╔════╦════════╦════════╦════════╗ ║ id ║ field1 ║ field2 ║ field3 ║ ║ 1 ║ do ║ re ║ me ║ ║ 2 ║ fa ║ ║ so ║ ║ 3 ║ la ║ te ║ ║ ║ 4 ║ da ║ re ║ ║ ╚════╩════════╩════════╩════════╝ 

output:

 id 4 field1 4 field2 3 field3 2 

I am trying to get a measure of field usage in a very dirty database that I am migrating. There are about 50 columns in this database, so I'm looking for an approach that does not include typing each column name.

I may also need to expand this search to non-NULL and non-empty, and ≠ 0 and ≠ “no” due to inconsistency in data storage - some fields were never used, and “no” were automatically filled.

This answer is close to what I need, but generates an SQL error, and I don't have enough reputation for comments: Calculate the number of NULL values ​​in each column in SQL

Answer 1

Just use count() :

 select count(field1), count(field2), count(field3) from table t; 

This is what count() does - it counts non-null values.

If you have an aversion to typing names, use metadata tables (usually information_schema.columns ) to get the column names. You can generate SQL as a query or copy the column names into a spreadsheet to generate code.

EDIT:

You can generate the code using:

 select group_concat('count(', column_name, ')' separate ', ') from information_schema.columns where table_name = <whatever> and table_schema = <whatever2>; 

Note that the little-known ability of group_concat() to accept multiple string arguments is used.

Answer 2

 SELECT count(field1) as cnt ,'field1' as field from tbl where field1 IS NOT NULL UNION all SELECT count(field2) as cnt ,'field2' as field from tbl where field2 IS NOT NULL union all SELECT count(field3) as cnt ,'field3' as field from tbl where field3 IS NOT NULL 

IF ONLY 3 FIXED FIELDS ABOVE SQL WILL WORK

Answer 3

Here is sqlfiddle

 SELECT COUNT(id), SUM(CASE WHEN field1 IS NOT NULL THEN 1 ELSE 0 END), SUM(CASE WHEN field2 IS NOT NULL THEN 1 ELSE 0 END), SUM(CASE WHEN field3 IS NOT NULL THEN 1 ELSE 0 END) FROM table1;