根据条件计算MYSQL查询中的百分比 [英] Calculating Percentage within MYSQL query based on conditions

问题描述

希望你能帮上忙，这让我无所适从

hope you can help, this is driving me up the wall

我需要计算问题失败的百分比，但这需要根据地理区域来缩小范围，并提出针对这些问题的产品.

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.

我有:

$CA002 = "( SELECT ROUND(100 *  (SELECT count(CA002Result) from Data_Table where (CA002Result='Fail'))/count(CA002Result),2) from Data_Table) AS 'CA002 %'";

有效"但只是将整个记录作为整体"进行计算

Which 'works' but just calculates against the whole set of records as an 'overall'

我正在尝试:

$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 %'";

还有:

$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 %'";

也可以尝试此操作，但会收到MYSQL语法错误.

Also Trying this, but get a MYSQL syntax Error.

$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 %')";

无论尝试什么，都会遇到错误，我似乎无法弄清楚需要放在哪里.

and am just getting errors no matter what I try, I just can't seem to work out what I need to put where.

尝试以下答案:

$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 %'";

但出现以下错误:

MySQL错误:您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册以获取正确的语法，以在'(SELECT ROUND(100 *(SELECT count(CA002Result)from Data_Table where(CA002Re'at 1 line)

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

非常感谢您的帮助.

编辑-使用

$CA001 ="ROUND ((SELECT 100 * SUM(IF(CA001Result='Fail', 1, 0)) / COUNT(CA001Result) 
FROM Data_Table
WHERE  Area='$Area'),2) as 'CA001 %'";

非常感谢您的帮助！

推荐答案

尝试一下

SELECT 100 * SUM(IF(CA001Result='Fail', 1, 0)) / COUNT(CA001Result) as 'CA002 %'
FROM Data_Table
WHERE '$product' AND Area='$Area'

这篇关于根据条件计算MYSQL查询中的百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！