title: Calculating Percentage within MYSQL query based on conditions
body: <p>hope you can help, this is driving me up the wall</p>
  
  <p>I need to calculate the percentage of times a question has been failed, but this needs to be narrowed down by the geographical area, and product these questions are being asked against.</p>
  
  <p>I have : </p>
  
  <pre><code>$CA002 = "( SELECT ROUND(100 *  (SELECT count(CA002Result) from Data_Table where (CA002Result='Fail'))/count(CA002Result),2) from Data_Table) AS 'CA002 %'";
  </code></pre>
  
  <p>Which 'works' but just calculates against the whole set of records as an 'overall'</p>
  
  <p>I'm trying :</p>
  
  <pre><code>$CA001 = "( SELECT ROUND(100 *  (SELECT count(CA001Result) from Data_Table where (CA001Result='Fail' AND Area ='$Area'))/count(CA001Result) from Data_Table WHERE (Area='$Area'),2) AS 'CA001 %'";
  </code></pre>
  
  <p>And Also :</p>
  
  <pre><code>$CA001 = "( SELECT ROUND(100 * (SELECT count(CA001Result ) from Data_Table where (CA001Result='Fail' AND Product='$product' AND Area='$Area'))      
      /     count(CA001Result WHERE Product = '$product' AND Area='$Area'),2) from Data_Table) AS 'CA001 %'";
  </code></pre>
  
  <p>Also Trying this, but get a MYSQL syntax Error.</p>
  
  <pre><code>$CA001 = "( SELECT ROUND(100 *  (SELECT count(CA001Result) from Data_Table where (CA001Result='Fail'))/ (SELECT (count(CA001Result) from Data_Table WHERE (Area='$Area')),2) AS 'CA001 %')";
  </code></pre>
  
  <p>and am just getting errors no matter what I try, I just can't seem to work out what I need to put where.</p>
  
  <p>TRied the below answer:</p>
  
  <pre><code>$CA001 = "(SELECT 100 * SUM(IF(CA001Result='Fail', 1, 0)) / COUNT(CA001Result) as 'CA001 %' WHERE  Area='$Area')";
  
  $CA002 = "( SELECT ROUND(100 *  (SELECT count(CA002Result) from Data_Table where (CA002Result='Fail'))/count(CA002Result),2) from Data_Table) AS 'CA002 %'";
  </code></pre>
  
  <p>but am getting the below as an error:</p>
  
  <p>MySQL ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' ( SELECT ROUND(100 * (SELECT count(CA002Result) from Data_Table where (CA002Re' at line 1</p>
  
  <p>Any help GREATLY appreciated, thankyou.</p>
  
  <p>Edit - Cracked it using : </p>
  
  <pre><code>$CA001 ="ROUND ((SELECT 100 * SUM(IF(CA001Result='Fail', 1, 0)) / COUNT(CA001Result) 
  FROM Data_Table
  WHERE  Area='$Area'),2) as 'CA001 %'";
  </code></pre>
  
  <p>Thanks so much for the help!</p>