SQL Query to Find NULL and Non-null Percentage of Column

Here are SQL queries to find the percentages of Null and Non-null columns. In production, these percentages indicate data quality.

SQL to Find Non-Null Percentage

use sakila;

create table non_null(name char(10));

insert into non_null values ('ramu'),
('krishna'),
('ravi'),
(NULL),
(NULL);


select * from non_null;

SELECT 
    (
COUNT(name) * 100.0 / COUNT(*)
) AS non_null_percentage
FROM 
    non_null;

Explanation

• COUNT(name): Counts the number of non-null values in the name.
• COUNT(*): Counts the total number of rows in non_null.
• * 100.0 / COUNT(*): Calculates the non-null percentage by dividing the count of non-null values by the total rows and multiplying by 100.

The result will give you the percentage of the column non-null values.

“Education is the key to unlock the golden door of freedom.”

George Washington

SQL to Find NULL percentage

SELECT 
    (
COUNT(CASE WHEN name IS NULL THEN 1 END) * 100.0 / COUNT(*)
) AS null_percentage
FROM 
    non_null;

-- OR
SELECT 
    (
    COUNT(*) - COUNT(name)
    ) * 100.0 / COUNT(*) AS null_percentage
FROM 
    non_null;

Explanation

• COUNT(CASE WHEN name IS NULL THEN 1 END): Counts how many rows have a null name. The CASE statement assigns 1 each null. COUNT Totals those 1 values to find the number of nulls.
• COUNT(*): Counts the total number of rows in non_null.
• * 100.0 / COUNT(*): This calculates the percentage of null values by dividing the number of nulls by the total rows and multiplying by 100.

The result will give you the percentage of column NULL values.