Count records where column is not null using a single query

Feedback


Solution 1:


COUNT(expr)

Provides a tally of the quantity of non-NULL values for expr in the rows obtained through a SELECT statement.

The ensuing inquiry will provide tallies of non-null values for every column.

SELECT
COUNT(*)     AS Total,
COUNT(X_N)   AS NNC_N,
COUNT(X_Pad) AS NNC_Pad
FROM table;

Obtain the list of corresponding columns in a table by implementing this query.

SELECT COLUMN_NAME
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''
AND   TABLE_NAME = ''
AND   COLUMN_NAME LIKE 'x_%';
-- Output:
-- X_N
-- X_Pad
-- X_Server

This query can be utilized to construct another query.

SELECT CONCAT('SELECT ', GROUP_CONCAT('COUNT(`', COLUMN_NAME, '`) AS `NNC of ', COLUMN_NAME, '`'), ' FROM `', TABLE_NAME, '`')
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''
AND   TABLE_NAME = ''
AND   COLUMN_NAME LIKE 'x_%';
-- Output (added whitespace for readability):
-- SELECT
-- COUNT(`X_N`)      AS `NNC of X_N`,
-- COUNT(`X_Pad`)    AS `NNC of X_Pad`,
-- COUNT(`X_Server`) AS `NNC of X_Server`
-- FROM ``

Alternate:

SELECT GROUP_CONCAT('SELECT '', COLUMN_NAME, '' AS `Col`, COUNT(`', COLUMN_NAME , '`) AS `NNC` FROM `', TABLE_NAME , '`' SEPARATOR ' UNION ALL ')
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''
AND   TABLE_NAME = ''
AND   COLUMN_NAME LIKE 'x_%';
-- Output (added whitespace for readability):
-- SELECT 'X_N'      AS `Col`, COUNT(`X_N`)      AS `NNC` FROM `` UNION ALL 
-- SELECT 'X_Pad'    AS `Col`, COUNT(`X_Pad`)    AS `NNC` FROM `` UNION ALL 
-- SELECT 'X_Server' AS `Col`, COUNT(`X_Server`) AS `NNC` FROM ``


Solution 2:


Query the database table “db.tbl” to obtain the count of “X_N”, “X_Pad”, and “X_Server”.

can be ok.

In case you are searching for a way to create this SQL, you have the option to utilize.

SELECT CONCAT("select ", GROUP_CONCAT("count(",column_name, ")"), " from ", table_schema, '.', table_name)
FROM information_schema.columns
WHERE table_schema='db' AND table_name='tbl' AND column_name LIKE 'X_%';


Solution 3:


Maybe this could work? (not yet verified)

SELECT 
(SELECT COUNT(id) FROM My_Table WHERE X_1 != NULL) as x1,
(SELECT COUNT(id) FROM My_Table WHERE X_2 != NULL) as x2,
(SELECT COUNT(id) FROM My_Table WHERE X_3 != NULL) as x3,
(SELECT COUNT(id) FROM My_Table WHERE X_4 != NULL) as x4;

Frequently Asked Questions