Count how many fields are blank in a MYSQL row

Question

Count how many fields are blank in a MYSQL row

Navigation

#1 by (5 votes)
#2 by (0 votes)

4

I have a registration table with 53 columns (address, phone, etc.).

I wanted to set up a query that would bring me how many of these columns are empty or NULL, how can I do this?

mysql sql select

asked by anonymous 11.10.2018 / 19:49

2 answers

Rearrange data frame in a list using dplyr in R What problems can occur when using the "http://tempuri.org/" namespace in our webServices?

score 5 · Answer 1

I do not know if I understand very well, but I believe that if you count the records using the COUNT (*) clause and where the columns are null IS NULL and empty = '' , you can find what you expect. For example:

SELECT COUNT(*) FROM tabela WHERE (coluna1 IS NULL or coluna1 = '') 
AND (coluna2 IS NULL or coluna2 = '') 
AND (coluna3 IS NULL or coluna3 = '') ... -- AND a quantidade de colunas que você desejar adiocionar na sua query.

I hope I have helped!

score 0 · Answer 2

Based on what I understood ( how many fields of each user were not filled) , I made the following code (take a look at this functional fiddle ):

create table tabela_colunas (id int, coluna varchar(100));
CREATE TABLE tabela_contador (id_na_tabela int(10), total int(1));

SET @id_tabela:=1, @total:=(SELECT COUNT(*) FROM tabela_colunas);

insert into tabela_contador (id_na_tabela, total)
select id, 0 as total from tabela;

WHILE @id_tabela <= @total DO

    set @sql := concat('update tabela_contador tc join tabela t on tc.id_na_tabela = t.id set tc.total = tc.total + 1 where ', (select coluna from tabela_colunas where id = @id_tabela), ' is null or ', (select coluna from tabela_colunas where id = @id_tabela), ' = \'\'');

    PREPARE myquery FROM @sql;
    EXECUTE myquery;

    SET @id_tabela = @id_tabela + 1;    
END WHILE;

select * from tabela_contador;

I followed the following logic:

I created a temporary table to store the column names;
I created another temporary table to store the totals of each occurrence of the original table;
For each of the columns, a total is added (when id is the same and the field has no value);
Finally, query the data table.