mysql计算已完成列的行数，还计算不完整列的行数？

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

Answer 1

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.