Count non-empty fields mysql

Ask Time：2013-09-07T23:48:55 Author：Marius Prollak

I want to count how many fields of a specific fieldset are empty in mysql, I've found some examples but they all go through the whole table.

Basically I have 8 fields,

listing_photo_1 to listing_photo_8, I want to get the count of how many of them are filled.

I tried:

$result=mysql_query("SELECT count(*) as total from listings 
                       WHERE listing_photo_1 IS NOT NULL AND 
                             listing_photo_2 IS NOT NULL AND 
                             listing_photo_3 IS NOT NULL AND 
                             listing_photo_4 IS NOT NULL AND 
                             listing_photo_5 IS NOT NULL AND 
                             listing_photo_6 IS NOT NULL AND 
                             listing_photo_7 IS NOT NULL AND 
                             listing_photo_8 IS NOT NULL AND 
                             pmpid = '$pmpid'");

$data=mysql_fetch_assoc($result);
echo $data['total'];

Which results in: 1

To clarify the result I am expecting:

listing_photo_1: filled

listing_photo_2: filled

listing_photo_3: filled

listing_photo_4: empty

listing_photo_5: empty

listing_photo_6: empty

listing_photo_7: empty

listing_photo_8: empty`

The result should be 3

Author:Marius Prollak，eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/18675069/count-non-empty-fields-mysql

Gordon Linoff :

You code attempts to count the number of rows where all the fields a not null. You should be using is not null rather than just not null.\n\nTo count the number of fields, use this:\n\nSELECT sum((listing_photo_1 IS NOT NULL) +\n (listing_photo_2 IS NOT NULL) +\n (listing_photo_3 IS NOT NULL) +\n (listing_photo_4 IS NOT NULL) +\n (listing_photo_5 IS NOT NULL) +\n (listing_photo_6 IS NOT NULL) +\n (listing_photo_7 IS NOT NULL) +\n (listing_photo_8 IS NOT NULL)\n ) as total\nfrom listings\nWHERE pmpid = '$pmpid';\n\n\nTo count the number of rows:\n\nSELECT count(*) as total\nfrom listings\nWHERE listing_photo_1 IS NOT NULL AND\n listing_photo_2 IS NOT NULL AND \n listing_photo_3 IS NOT NULL AND \n listing_photo_4 IS NOT NULL AND \n listing_photo_5 IS NOT NULL AND \n listing_photo_6 IS NOT NULL AND \n listing_photo_7 IS NOT NULL AND \n listing_photo_8 IS NOT NULL AND \n pmpid = '$pmpid'\";\n\n\nEDIT:\n\nIf they are blank, use logic like this:\n\nSELECT sum((listing_photo_1 IS NOT NULL and listing_photo_1 <> '') +\n (listing_photo_2 IS NOT NULL and listing_photo_2 <> '') +\n (listing_photo_3 IS NOT NULL and listing_photo_3 <> '') +\n (listing_photo_4 IS NOT NULL and listing_photo_4 <> '') +\n (listing_photo_5 IS NOT NULL and listing_photo_5 <> '') +\n (listing_photo_6 IS NOT NULL and listing_photo_6 <> '') +\n (listing_photo_7 IS NOT NULL and listing_photo_7 <> '') +\n (listing_photo_8 IS NOT NULL and listing_photo_8 <> '')\n ) as total\nfrom listings\nWHERE pmpid = '$pmpid';\n",

2013-09-07T15:53:54

Count non-empty fields mysql