SQL Count not null values in every table column

In this post:

SQL count null and not null values for several columns
MySQL select count null values per column
- Count by multiple selects
MySQL count values for every table and schema
Oracle SQL select count null values per column
- Count by multiple selects
- Count by single select query
- Oracle count null and not null values for several columns

If you need to check the number of null values per column for tables with many columns or for many tables you can use meta database meta information. In most databases there is technical and statistical information. Despite that you may need to review this faster - for example after huge data import or another event.

MySQL select count null values per column

Count by multiple selects

for table named person with owner powner generate SQL query which counts all values(not null) per column.
Set concatenation by pipe(in order to work || concatenation):

set sql_mode=PIPES_AS_CONCAT;

Run this SQL (first replace POWNER and PERSON with your names)

SELECT 
'select count(' || 
column_name ||
 '),     ''' ||
 column_name ||
 ''' from powner.person union' AS table_name
FROM information_schema.columns
WHERE table_schema = 'powner' 
AND table_name = 'person'

Copy the result and paste it in new Query tab
Delete the last union and run the result query

select count(id), 'id' from powner.person union
select count(version), 'version' from powner.person

Result

Count(id)	id
10	id
10	version
7	name

MySQL count values for every table and schema

You can count the null or not null values for every table and schema in MySQL. This can be done in two step process.

First lets prepare script reading every table and column in MySQL instance:

set sql_mode=PIPES_AS_CONCAT;

SELECT 
'select count(' || 
column_name ||
 '),     ''' ||
table_schema || ''',''' || table_name || ''',''' ||column_name ||
 ''' from '|| table_schema || '.' || table_name || ' union' AS table_name
FROM information_schema.columns

The result will be another query which should be executed:

select count(id),     'test','aboys','id' from test.aboys union
select count(name),     'test','aboys','name' from test.aboys union
select count(relation),     'test','aboys','relation' from test.aboys
...

You need to clean it up a bit:

remove the final union
remove all reserved keyword which are column names like - index, user etc or just add gravis to the name in order to avoid mistakes

user -> user

Execute the query and use the result. In the final result you will have the table name, the schema and the column name. This can be very useful if you want to find inconsistent or corrupted data:

Count values	Schema	Table	Column
20	test	aboys	id
20	test	aboys	name
12	test	aboys	relation
5	test	agirls	id
5	test	agirls	name

MySQL count null and not null values for several columns

The same trick apply for MySQL(you can use this solution also for Oracle):

SELECT 
	COUNT(colx) x_not_null, -- count colx not null values
	COUNT(coly) y_not_null, -- count coly not null values
	COUNT(*) - COUNT(colx) x_null, -- count colx null values
	COUNT(*) - COUNT(coly) y_null, -- count coly null values
	COUNT(CASE WHEN colx IS NOT NULL AND coly IS NOT NULL THEN 1 END) x_y_not_null, -- count colx and coly not null values
	COUNT(CASE WHEN colx IS NULL AND coly IS NULL THEN 1 END) x_y_null-- count colx and coly  null values
FROM table

The result:

x_not_null	y_not_null	x_null	y_null	x_y_not_null	x_y_null
12	5	5	11	1	1

Oracle SQL select count null values per column

This examples are tested with Oracle. They are using standard SQL so they will work also on MySQL or any other DB which is following SQL standards.

Count by multiple selects

for table named person with owner powner generate SQL query which counts all values(not null) per column.
Run this SQL (first replace POWNER and PERSON with your names)

SELECT 'select count('
     || column_name
     || '),     ''' ||     
     column_name || ''' from POWNER.PERSON union' AS table_name
   FROM all_tab_cols
   WHERE table_name = 'PERSON'
   AND owner        = 'POWNER'

Copy the result and paste it in new Query tab
Delete the last union and run the result query

select count(id), 'id' from powner.person union
select count(version), 'version' from powner.person

Result

Count(id)	id
10	id
10	version
7	name

So the column name only 7 rows has value and the rest 3 are nulls

Count by single select query

You can do the same as above with a single query by using LISTAGG oracle function.

Change POWNER and PERSON with your owner and table name.

SELECT 'select '
 || LISTAGG( column_name, ' ' ) WITHIN GROUP (
ORDER BY table_name)
 || '''PERSON'' from POWNER.PERSON' "names"
FROM
 (SELECT *
 FROM
   (SELECT 'count('
     || column_name
     || '), '         AS column_name,
     'PERSON' AS table_name
   FROM all_tab_cols
   WHERE table_name = 'PERSON'
   AND owner        = 'POWNER'
   )
 )
GROUP BY table_name

Get the result and run it in new tab query:

This query is the result of the previous step. It will contain all columns for your table. The final result would be count of all non null values per column.

SELECT 
COUNT(ID), 
COUNT(VERSION), 
COUNT(NAME), 
 'PERSON'
FROM POWNER.PERSON

final result(as you can notice the result is transposed - not vertically but horizontally)

Count(id)	Count(version)	Count(name)
10	10	7

So if you want to transpose the result you can do it by using pivot function:

SELECT 
id, VERSION,name
FROM powner.person
 UNPIVOT (Count(id) FOR id IN (VERSION AS 'version'
,name AS 'name')
)
)

Oracle count null and not null values for several columns

You can use count in order to get information about the null and not null values in your tables. In this example you are counting the null and not null values for a column. You have also sum of null values of several columns. For example:

COUNT(colx) - this will count all non null values for column colx in Oracle(but the same will apply for MySQL or anu other SQL standard DB.
COUNT(`*) - COUNT(colx) - using this will return the number of null values in column colx
COUNT(CASE WHEN colx IS NULL AND coly IS NULL THEN 1 END) x_y_null - return null values in more columns. this can be used when you have related information like price and quantity:

SELECT 
	COUNT(colx) x_not_null, -- count colx not null values
	COUNT(coly) y_not_null, -- count coly not null values
	COUNT(*) - COUNT(colx) x_null, -- count colx null values
	COUNT(*) - COUNT(coly) y_null, -- count coly null values
	COUNT(CASE WHEN colx IS NOT NULL AND coly IS NOT NULL THEN 1 END) x_y_not_null, -- count colx and coly not null values
	COUNT(CASE WHEN colx IS NULL AND coly IS NULL THEN 1 END) x_y_null-- count colx and coly  null values
FROM table

Previous post on SQL:

If you need quick reference on SQL selects and joins:
Frequent SQL commands select

If you want to need to update or delete data/tables:
Frequent SQL commands DML and DDL

> Python Basics

> Advanced Tutorials

> Python Errors

> Pandas Advanced

> Pandas Count

> Pandas Column

> Pandas Basics

> Pandas DataFrame

> Pandas Row

> User Interface

> Advanced

> Troubleshoot

> Video & Sound

> Linux Commands

> MySQL

> SQL Basics

> Python

> DB apps

> JupyterLab

> Jupyter Tips

> Jupyter Display

> Regex in Text Editor

> Regex Basics

> Regex Match

> Regex Date

> PyCharm Advanced

> Git and PyCharm

> PyCharm Error

> PyCharm Tips

> Linux Mint Applications

> VIrtual Machine

> Miscellaneous

> Java

> Automation

> Windows

> Office

> Cheat Sheet

MySQL select count null values per column

Count by multiple selects

MySQL count values for every table and schema

MySQL count null and not null values for several columns

Oracle SQL select count null values per column

Count by multiple selects

Count by single select query

Oracle count null and not null values for several columns

Previous post on SQL: