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

Asked

Viewed 293 times

1

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

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

It is possible to do this?

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

ID terreno|camposembranco
1         | 5
2         | 2
3         | 0
mysql select count

  • I don’t understand if you want to check this by row or in the table as a whole.

  • in the table as a whole, are land registrations, then I need to know how many fields have not been filled, for example, not filled only the city, then I will show that there is 1 field to be filled, I will edit the question to get better

  • So it’s by line. I mean, you want to know that the city is missing for land X. Or whether the city is blank for ALL land?

  • sorry, that, per line, ID line type 2 missing 5 fields, ID line 7 missing 8

1 answer

1

There are other ways to do it, but 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 ai performing the sum in a single field (NULL SUM).

Just change the columns and table name for your template.

Follow a model example..

SQL Fiddle

  • so I have to make a query for each column ne? there is no one way to do it 1 time

  • You can put everything in the same query Leandro. Where is 'COLUNA1', 'COLUNA2' would be your own columns. You’ll only need to do it more than once if it’s by table. In the example I put there are 3 columns I am searching using the same Select, da uma olhada la

Browser other questions tagged mysql select count

You are not signed in. Login or sign up in order to post.