MySQL COUNT NULL content by column, GROUP by column

Question

How can I count non-null entries by field/column? I see several answers to count by row but can't hack how to do so 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'm trying to get a gauge on field usage in a very dirty database I am migrating. There's about 50 columns in this database so I am looking for an approach that doesn't involve typing out each column name.

I might also have to extend that search to non-NULL & is-not-empty & ≠ 0 & ≠ "no" because of inconsistencies in data storage — some fields were never used but auto-filled with "no".

This answer looks close to what I need but generates an SQL error and I don't have enough reputation to comment: Count number of NULL values in each column in SQL

Answer 1

Just use count():

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

That is what count() does -- it counts non-NULL values.

If you have an aversion to typing out names, then use the metadata tables (typically information_schema.columns) to get the column names. You can generate the SQL as a query, or copy the column names into a spreadsheet to generate the 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 this uses the little known ability of group_concat() to take multiple string arguments.

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 THERE ARE ONLY 3 FIXED FIELD ABOVE SQL WILL WORK