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

Updated: I am using MySQL not SQL if that makes a difference. — Slam
– Slam, Commented Mar 16, 2015 at 12:29

Gordon Linoff · Accepted Answer · 2015-03-17 11:56:13Z

3

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.

edited Mar 17, 2015 at 11:56

answered Mar 16, 2015 at 11:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ankit Bajpai Over a year ago

@gordon linoff, i think user wants row wise data, not column wise.

Slam Over a year ago

I get an SQL syntax error when I run this. Am I supposed to replace table t with my table name somewhere? Also with 50 columns names I am looking for a programmatic way to run this WITHOUT typing each of these names by hand.

Slam Over a year ago

@Gorden, "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..." I know how to use count(). Figuring out how to get column names via SQL query is what i am missing. Could you edit your answer to include that?

Slam Over a year ago

@Gordon's answer ended up being the correct one and I was too new at the time to understand it. And kudos for the meta-answer of using SQL to output new query language.

sumit · Accepted Answer · 2015-03-16 12:03:34Z

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

edited Mar 16, 2015 at 12:03

answered Mar 16, 2015 at 11:54

sumit

15.5k14 gold badges75 silver badges113 bronze badges

2 Comments

Slam Over a year ago

There's about 50 fields. This would work if I wanted to type them all out.

Salman Arshad Over a year ago

You don't really need UNION ALL unless some column names are same, which is not possible.

Alex · Accepted Answer · 2015-03-16 13:38:24Z

0

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;

edited Mar 16, 2015 at 13:38

answered Mar 16, 2015 at 13:08

Alex

17.3k1 gold badge31 silver badges52 bronze badges

Collectives™ on Stack Overflow

MySQL COUNT NULL content by column, GROUP by column

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related