Collect count from any missing data in rows from MySql

Issue

I currently have a counter that will give a total number of entries that is missing information for NAME in the database. I have 3 more fields for this (Name, Address, Phone, Email). I would like the count to show if any of that info is missing, not just the names. (below is what I have so far).

What I have is this

select count(ifnull(name,' ')) AS count FROM `customer` WHERE name IN (' ') != ' '

Another words, if I have for example the following, then the counter would say 3 and also only echo the ones that have the missing fields

| id | Name |   Address  |  Phone    |    Email     |
|----|------|------------|-----------| -------------|
| 1  | bob  |  123 main  | 555-5555  |  [email protected] |
| 2  |      |  123 main  | 555-5555  |  [email protected] |
| 3  | ann  |  123 main  |           |  [email protected] |
| 4  | tod  |            | 555-5555  |  [email protected] |

Solution

Use COUNT(*) to count the number of rows that meet the WHERE condition.

To count if any of the fields are missing, check them all in the WHERE clause.

SELECT COUNT(*) 
FROM customer
WHERE IFNULL(name, '') = '' or IFNULL(address, '') = '' or IFNULL(phone, '') = '' or IFNULL(email, '') = ''

Answered By – Barmar

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published