Counting non-empty values in each column of a tabl

问题:

I am looking for missing data in a data migration project, and this report will help me immensely.

Given a MySQL table, I would like to count all the empty (NULL or '') values in each row of that table. The output would be a list of column names and a count of empty or non-empty rows for each column. This data is something I would manually compare to the source tables - manually because I expect few counts to match up exactly and column names are completely different between the source and imported tables.

I have about 30 tables to check, a few with 100 columns. I have direct MySQL access from my PC, but no access to run any scripts on the server with the database.

Example for TableA

Col1 Col2 Col3
'XX' NULL 'XX'
'XX' NULL ''
'XX' 'XX' 'XX'
'XX' ''   'XX'

The report I would want is (for non-empty counts with '' counted as empty):

Col1: 4
Col2: 1
Col3: 3

回答1:

You can use the following query for each table

SELECT COUNT(*), COUNT(col1) as col1, COUNT(col2) as col2
FROM TABLE1

For getting all columns for a specific table you should run query

 select column_name from information_schema.columns where TABLE_NAME='TABLE1';

Results of this query you can use for auto generation queries like the first one.

回答2:

COUNT counts empty strings too, so your query should look like this:

SELECT COUNT( NULLIF( col1, '' ) ), COUNT( NULLIF( col2, '' ) ), ...

回答3:

Count rows only which has values (Skipping Null/Empty rows)!!!

SELECT COUNT( NULLIF( Column_Name, '' ) ) from Table_name

回答4:

this worked for me

SELECT count( NULLIF( doctor_id, '' )) as doctor_count,count( NULLIF( chemist_id, '' )) as chemistcount from table_name WHERE employee_id="20";