mysql count number of rows with completed columns and also count the number of rows with incomplete columns?

Question

i am using this script to count how many rows exist in my table that have completed columns like so

<?php include 'config.php';

    $q = "SELECT * FROM supplier_session WHERE form1_completed = 'Yes' AND form2_completed = 'Yes' AND form3_completed = 'Yes' AND form4_completed = 'Yes'" or die (mysql_error());
    $result = mysql_query($q);
    $count = mysql_num_rows($result);
?>

<?php echo "(<font color=red>$count</font>)"; ?>

this script says that if 'form1_completed, form2_completed, form3_completeed and form4_compelted all = 'yes' then to count the number of rows.

Now what i want to do, is have a seperate count that shows the number of rows that are incomplete, so baring in mind that some rows may only have 'form1_completed' as 'yes' and 'form2_completed' as 'no'. i need to basically count any rows that do not have all four columns completed or set as yes, so 'form1_complted', 'form2_completed', 'form3_completed', 'form4_completed' if these are not all yes then to count the rows

could someone please show me how i can do this? thanks

Count the total rows in db, and subtract the number from your previous query — Steve, May 02 '14 at 11:02
i cant because if i do that then it will only count those which do no = yes, but for example if only 'form1_completed' is 'yes' then all the other 3 columns are no, then this row wont get counted as ive put form1_completed != 'yes', im basically saying i want to count this row even if only one column is completed but not the other 3, as long as all four are not yes — user3584968, May 02 '14 at 11:04
or get both results in one query : http://stackoverflow.com/questions/12789396/how-to-get-multiple-counts-with-one-sql-query — Steve, May 02 '14 at 11:04
@user3584968 So whats wrong with my suggestion to count ALL rows and subtract the completed total you already have? — Steve, May 02 '14 at 11:05
i dont know how to do that, im sorry could u show me please? — user3584968, May 02 '14 at 11:08

score 1 · Answer 1 · answered May 02 '14 at 11:19

Try using conditional aggregation:

SELECT sum(form1_completed = 'Yes' AND form2_completed = 'Yes' AND
           form3_completed = 'Yes' AND form4_completed = 'Yes'
          ) as NumAllCompleted,
       sum(not (form1_completed = 'Yes' AND form2_completed = 'Yes' AND
                form3_completed = 'Yes' AND form4_completed = 'Yes'
               )
          ) as NumIncomplete
FROM supplier_session;

This assumes that the completed flags never take on NULL values.

Note: it is usually a bad idea to store "arrays" over values in multiple columns. You should have a junction/association table for each form. This would have one row for user/form combination, along with the status of that form. And it might have other information such as the date/time the form was completed.

score 0 · Answer 2 · edited May 23 '17 at 12:05

If the fields do contain NULL values (say for when they haven't completed the form you could select everything and loop it as shown below though I would make the fields default to No and use Gordon Linoff answer myself.

<?php include 'config.php';
        $q = "SELECT * FROM supplier_session" or die (mysql_error());
        $result = mysql_query($q);
        $count = mysql_num_rows($result);
        $completed = 0;
        foreach($result as $check) {
            if(($check['form1_completed'] = 'Yes') && ($check['form2_completed'] = 'Yes') && ($check['form3_completed'] = 'Yes') && ($check['form4_completed'] = 'Yes')) {
             $completed++;
             }
        }

        $count // Total
        $completed // Completed
        $incomplete = $count - $completed; // Not Completed

        echo "(<font color=red>$count</font>)"; 
    ?>

mysql count number of rows with completed columns and also count the number of rows with incomplete columns?

2 Answers2