英文:
SQL query to calculate a percentage of a particular String in a column out of all rows
问题
我有一个非常简单的包含Status_var和description的表格。你可以在这里看到示例：http://sqlfiddle.com/#!18/3e0d1/5
我需要能够计算Status_var列中出现多少个“Pass”字符串并计算百分比。示例表格如下：
在上面的示例中，我们有2个“Pass”和1个“Fail”出现。结果应该是66%。
我在这个帖子中找到了一个非常相似的解决方案：
https://dba.stackexchange.com/questions/311416/mysql-getting-the-percentage-of-an-occurrence
我尝试在sqlfiddle中实现它，但它不起作用：
http://sqlfiddle.com/#!18/3e0d1/22
英文:
I have a very simple table that contains Status_var and description. You can see example here:
http://sqlfiddle.com/#!18/3e0d1/5
I need to be able to count how many 'Pass' string occurences are in Status_var column and calculate percentage. Example table below:
In the above example, we have 2 "Pass" and 1 "Fail" occurences. The result should be 66%.
I have found a very simmillar solution to this problem in this post:
https://dba.stackexchange.com/questions/311416/mysql-getting-the-percentage-of-an-occurrence
And I tried to implement it in sqlfiddle:
http://sqlfiddle.com/#!18/3e0d1/22
But it does not work
答案1
得分: 3
根据你提供的链接，我会选择以下代码：
SELECT SUM(CASE WHEN Status_var='Pass' THEN 1 ELSE 0 END) * 100 / COUNT(*)
FROM ForgeRock;
以获得结果为66。
英文:
For your mentioned link i would go with:
SELECT SUM(CASE WHEN Status_var='Pass' THEN 1 ELSE 0 END) * 100 / COUNT(*)
FROM ForgeRock;
To get the result 66.
答案2
得分: 2
这可以更加高效，因为只会计算Status_var = 'Pass'的情况：
select 'Pass' as Status_var, count(case when Status_var = 'Pass' then 1 end )*100/ count(*) AS PERCENT
from ForgeRock
另外，在Status_var上创建一个索引将会有帮助。
英文:
This could be more efficient, since will count only the ones with Status_var = 'Pass' :
select 'Pass' as Status_var, count(case when Status_var = 'Pass' then 1 end )*100/ count(*) AS PERCENT
from ForgeRock
Also an index On Status_var will be helpfull
答案3
得分: 1
以下是代码部分的翻译：
Pretty simple approach is adding another `SELECT` around your query specifying the appropriate condition (you'll have to give the calculated column a name for); this might look like:
SELECT * FROM ( --
select Status_var, count(Status_var)*100/sum(count(*)) over() AS percentage
from ForgeRock
group by Status_var
) sp WHERE status_var = 'Pass'
EDIT: Your second query you adopted from the other answer actually seems to be fine – on mySQL! See sqlfiddle – but you need to set mySQL as the database, you tried on MS SQL which doesn't seem to accept comparisons at this place; it seems to interpret the equal-sign as assignment, as the query
SELECT status_var, Status_var = 'Pass' FROM ForgeRock
reveals...
英文:
Pretty simple approach is adding another SELECT around your query specifying the appropriate condition (you'll have to give the calculated column a name for); this might look like:
SELECT * FROM ( -- vvvvvvvvvvvvv (!)
select Status_var, count(Status_var)*100/sum(count(*)) over() AS percentage
from ForgeRock
group by Status_var
) sp WHERE status_var = 'Pass'
EDIT: Your second query you adopted from the other answer actually seems to be fine – on mySQL! See sqlfiddle – but you need to set mySQL as database, you tried on MS SQL which doesn't seem to accept comparisons at this place; it seems to interpret the equal-sign as assignment, as the query
SELECT status_var, Status_var = 'Pass' FROM ForgeRock
reveals...
答案4
得分: 1
你可以尝试这个，它将返回通过/失败的百分比：
select Status_var, count(Status_var)*100/sum(count(*)) over()
from ForgeRock
group by Status_var
结果：
失败：33 通过：66
如果只需要通过的响应，请尝试以下代码：
select Status_var, perc from (
select Status_var, count(Status_var)*100/sum(count(*)) over() as perc
from ForgeRock
group by Status_var)t where Status_var='Pass';
结果：
结果：通过：66
英文:
You can try this it will return you percentage for both status pass/fail
select Status_var,count(Status_var)*100/sum(count(*)) over()
from ForgeRock
group by Status_var
Result :
> Fail : 33 Pass : 66
If need response only for pass then try this -
select Status_var,perc from (
select Status_var,count(Status_var)*100/sum(count(*)) over() as perc
from ForgeRock
group by Status_var)t where Status_var='Pass'
> Result : Pass : 66
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论