How to count how many fields are empty in the mysql table

Question

How to count how many fields are empty in the mysql table

Navigation

#1 by (1 votes)

1

I have a land register table with several columns (some of them: id, address, city, proposal, etc.) and would like to count how many fields are empty.

For example, the table has a total of 44 columns, if you only have 20 fields filled in, the sql query should bring the result to 24, which are either blank or null.

Is it possible to do this?

He has to do the land search and must present the table so that the person knows how many fields he has not filled in and is missing:

ID terreno|camposembranco
1         | 5
2         | 2
3         | 0

mysql select count

asked by anonymous 16.07.2018 / 16:31

1 answer

Draw with different percentages How to instantiate objects using literal object syntax?

score 1 · Answer 1

There are other ways to do it, however, I use it as follows:

    SELECT 
      ((CASE WHEN COLUNA1 IS NULL THEN 1 ELSE 0 END)
      + (CASE WHEN COLUNA2 IS NULL THEN 1 ELSE 0 END)
     .
     .
     .
     .
      + (CASE WHEN COLUNA5 IS NULL THEN 1 ELSE 0 END)) AS 'SOMA DE NULOS'
    FROM SUA_TABELA

This statement assigns 0 or 1 depending on whether the column is null or not. And there performing the sum in a single field (SOMA DE NULOS).

Just change the columns and the table name for your model.

Following is an example template.

SQL Fiddle