如何在SQL中以百分比显示标记?

Question

举个例子，我有这个数据

Total submission       170 
Passed Submission       32
Failed Submission      137
Skipped Submission       1

我需要能够显示：

1. 通过百分比
2. 失败百分比
3. 跳过百分比

有人可以帮助在SQL中实现这一目标吗？

Answer 1

因为我不知道表格的外观，这里有一个关于它的外观的示例

select
round(pass/total,2)*100 as passPercentage
, round(fail/total,2)*100 as failPercentage
, round(skip/total,2)*100 as skipPercentage
from table

Answer 2

它对我有用 - 答案是：

convert(float，secondset.PassedSubmit(*100/convert(float，FirstSet.TotalSubmit( asPassPercent，

(convert(float，FirstSet.TotalSubmit(-(Convert(float，Secondset.PassedSubmit(+Convert(float，Thirdset.SkippedSubmit(((*100/convert(float，FirstSet.TotalSubmit(( asFailPercent，

转换(浮点型，第三组。SkippedSubmit(*100/convert(float，FirstSet.TotalSubmit( asSkipPercent

Answer 3

这取决于数据的结构以及返回结果的方式。 如果您的表包含每个提交一行和一个status字段，则可以执行以下任一操作：

SELECT status, (COUNT(*) * 1.00) / (SELECT COUNT(*) FROM submission) AS status_pct
FROM submission
GROUP BY status
;
SELECT 
(SUM(CASE WHEN status = 'passed' THEN 1 ELSE 0 END) * 1.00) / COUNT(*) AS pass_pct,
(SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) * 1.00) / COUNT(*) AS fail_pct,
(SUM(CASE WHEN status = 'skipped' THEN 1 ELSE 0 END) * 1.00) / COUNT(*) AS skip_pct
FROM submission
;

SQL 小提琴