SQL oracle sql中如何计算百分比
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40547828/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to calculate percentage in oracle sql
提问by AnnaB
I have a table in which I have multiple IDs which can have a value or 0. The IDs come from different sources so I would like to know what is the percentage of IDs with the value 0 as a percentage of total IDs, for each source file.
我有一个表，其中有多个 ID，这些 ID 可以有一个值或 0。这些 ID 来自不同的来源，所以我想知道每个来源的值为 0 的 ID 占总 ID 的百分比是多少文件。
Sample Data:
样本数据：
ID Source
1 aaa
0 aaa
2 bbb
0 ccc
3 ccc
0 ccc
5 aaa
0 bbb
6 bbb
7 bbb
I need to display Output like:
我需要像这样显示输出：
CountOfIDs0 TotalIDs Source PercentageIDs0
2 3 ccc 66.6%%
1 3 aaa 33.3%%
1 4 bbb 25%
Thanks!
谢谢！
回答by mathguy
If you want a result like 66.6% rather than 66.7%, you would use trunc()rather than round()(although the latter is probably better). And you need to round a/b to three decimal places, so there is one left after you multiply by 100.
如果您想要像 66.6% 而不是 66.7% 这样的结果，您可以使用trunc()而不是round()（尽管后者可能更好）。并且需要将a/b四舍五入到小数点后三位，所以乘以100后还剩下一位。
Then, you can have both counts in one query, and you can add the percentage calculation also in the same query.
然后，您可以在一个查询中同时使用这两个计数，也可以在同一个查询中添加百分比计算。
select count(case when propkey = 0 then 1 end) countid0,
count(propkey) totalidcount,
source,
to_char(round(count(case when properkey = 0 then 1 end)/count(properkey), 3)*100)
|| '%' percentageids0
from......
回答by Tharunkumar Reddy
Apply roundfunction.
应用round功能。
select count(id) as TotalIDs ,Source, sum(case when id=0 then 1 end) countid0,
to_char((sum(case when id=0 then 1 end)/count(id))*100)||'%' as PercentageIDs0
from Table1 group by Source
回答by am2
I would do it that way:
我会这样做：
With MyRows AS (
SELECT 1 ID, 'aaa' SOURCE FROM DUAL UNION ALL
SELECT 0, 'aaa' FROM DUAL UNION ALL
SELECT 2, 'bbb' FROM DUAL UNION ALL
SELECT 0, 'ccc' FROM DUAL UNION ALL
SELECT 3, 'ccc' FROM DUAL UNION ALL
SELECT 0, 'ccc' FROM DUAL UNION ALL
SELECT 5, 'aaa' FROM DUAL UNION ALL
SELECT 0, 'bbb' FROM DUAL UNION ALL
SELECT 6, 'bbb' FROM DUAL UNION ALL
SELECT 7, 'bbb' FROM DUAL
)
SELECT
DISTINCT SOURCE,
SUM(CASE WHEN ID = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY SOURCE) ZERO_IDS,
COUNT(ID) OVER (PARTITION BY SOURCE) TOTAL_IDS,
(100 * SUM(CASE WHEN ID = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY SOURCE))/(COUNT(ID) OVER (PARTITION BY SOURCE)) PERCENTAGE
FROM MyRows
;
回答by rani
For Unique record you have to use DISTINCT Query
对于唯一记录，您必须使用 DISTINCT 查询
