How can I find which record in the table has the most populated fields?

Question

Or, perhaps, in a different way: how can I find which record in the table has the smallest null or empty fields?

Without the need to count each individual field separately, the table has 161 fields.

Answer 1

This can be done by building a dynamic query, for example:

SELECT id, (IF(col1 = NULL OR col1 = "", 1, 0) + IF(col2 = NULL OR col3 = "", 1, 0) + ... IF(coln = NULL OR coln = "", 1, 0) ) AS null_count FROM table_name ORDER BY null_count DESC LIMIT 1; 

This can be easily done by generating a new dynamic query using INFORMATION_SCHEMA.COLUMNS , and then running dynamic sql query . You may also need to increase the maximum output length from the GROUP_CONCAT function by setting the group_concat_max_len session level variable to a higher value.

 SET GLOBAL group_concat_max_len = 4294967295; SELECT @query1 := CONCAT('SELECT id, (', GROUP_CONCAT(CONCAT('IF(',COLUMN_NAME,' IS NULL OR ', COLUMN_NAME,' = "", 1, 0 ) ') SEPARATOR ' + '), ') AS null_count FROM table_name ORDER BY null_count DESC LIMIT 1') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME = 'table_name'; PREPARE stmt FROM @query1; EXECUTE stmt; DEALLOCATE PREPARE stmt; 

Example: SQLFiddle

Answer 2

DISCLAIMER: I misunderstood, thought the OP was looking for the column of the table with the most NULL. However, it may be useful to everyone.

Create a procedure like this:

 drop procedure if exists test_most_pop_field; DELIMITER $$ CREATE PROCEDURE test_most_pop_field(IN tableName varchar(100)) BEGIN DECLARE done INT DEFAULT 0; DECLARE sql_query VARCHAR(255); DECLARE cur CURSOR FOR SELECT CONCAT('INSERT INTO tmp_result(columnName, numberOfEmptyRows) SELECT "', COLUMN_NAME, '" AS columnName, SUM(IF(',COLUMN_NAME,' IS NULL OR ', COLUMN_NAME,' = "", 1, 0)) AS numberEmptyRows FROM ', TABLE_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME = tableName; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; DROP TABLE IF EXISTS tmp_result; CREATE TEMPORARY TABLE tmp_result(columnName varchar(100), numberOfEmptyRows int); OPEN cur; REPEAT FETCH cur INTO sql_query; IF NOT done THEN BEGIN SET @sql = sql_query; /*this extra step is necessary, cause otherwise it a syntax error, don't ask me why*/ PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END; END IF; UNTIL done END REPEAT; CLOSE cur; SELECT * FROM tmp_result ORDER BY numberOfEmptyRows DESC /*optionally LIMIT 1*/; END $$ DELIMITER ; 

Then call it with the name of the table you want to examine:

 CALL test_most_pop_field('yourTableName');