Oracle：如何通过查询获得总数的百分比？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13721093/
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
Oracle: how to get percent of total by a query?
提问by Revious
I have the following query. And the result it returns are very unespected:
我有以下查询。它返回的结果非常出乎意料：
select stato, (count(1) *100) / sum(1)
from LOG_BONIFICA
group by stato;
it returns 100 for all rows
它为所有行返回 100
回答by Kirill Leontev
count(1)is equal to sum(1)in your case.
count(1)sum(1)在你的情况下等于。
Try something like this:
尝试这样的事情：
18:39:36 SYSTEM@dwal> ed
Wrote file S:\tools\buffer.sql
1 select owner,
2 count(*) group_cnt,
3 sum(count(*)) over() total_cnt,
4 round(100*(count(*) / sum(count(*)) over ()),2) perc
5 from dba_objects
6 group by owner
7* order by 4 desc
18:39:57 SYSTEM@dwal> /
OWNER GROUP_CNT TOTAL_CNT PERC
------------------------------ ---------- ---------- ----------
SYS 31609 59064 53.52
PUBLIC 24144 59064 40.88
XDB 1660 59064 2.81
SYSTEM 597 59064 1.01
WMSYS 332 59064 .56
EXFSYS 312 59064 .53
IRKAZDATA 158 59064 .27
STRMADMIN 92 59064 .16
DBSNMP 55 59064 .09
RI 25 59064 .04
PASS 16 59064 .03
POTS 19 59064 .03
TI 11 59064 .02
STRMODS 11 59064 .02
OUTLN 10 59064 .02
APPQOSSYS 5 59064 .01
ORACLE_OCM 8 59064 .01
17 rows selected.
Elapsed: 00:00:00.16
update: or even simplier with ratio_to_report
更新：甚至更简单的 ratio_to_report
18:53:36 SYSTEM@dwal> ed
Wrote file S:\tools\buffer.sql
1 select owner,
2 round(100*ratio_to_report(count(*)) over (), 2) perc
3 from dba_objects
4 group by owner
5* order by 2 desc
18:54:03 SYSTEM@dwal> /
OWNER PERC
------------------------------ ----------
SYS 53.52
PUBLIC 40.88
XDB 2.81
SYSTEM 1.01
WMSYS .56
EXFSYS .53
IRKAZDATA .27
STRMADMIN .16
DBSNMP .09
RI .04
PASS .03
POTS .03
TI .02
STRMODS .02
OUTLN .02
APPQOSSYS .01
ORACLE_OCM .01
17 rows selected.
Elapsed: 00:00:00.20
回答by bonCodigo
Are you referring to the following? You need to find the total some separately.
您指的是以下内容吗？您需要单独找到一些总数。
- Reference SQLFIDDLE
Table:
桌子：
ID NAME AMOUNT STATE
1 john 1000 fl
2 jane 5000 ga
3 james 2000 ca
4 tom 6000 ga
5 tim 8000 fl
6 jim 2000 ga
7 kate 8000 fl
8 Hyman 3000 tx
Results 1:
结果 1：
STATE SUM(AMOUNT)
ca 2000
fl 17000
ga 13000
tx 3000
Final Query :
最终查询：
select t1.state, concat(
round(((sum(t1.amount)/t2.total))*100,2),
'%') as pct
from t1,
(select sum(amount) as total from t1) as t2
group by t1.state
;
Results:
结果：
STATE COUNT(T1.ID) SUM(T1.AMOUNT) TOTAL PCT
ca 1 2000 35000 5.71%
fl 3 17000 35000 48.57%
ga 3 13000 35000 37.14%
tx 1 3000 35000 8.57%
回答by yallie
Use ratio_to_reportanalytic function:
使用ratio_to_report分析函数：
SELECT STATO,
COUNT(1) STATO_COUNT,
RATIO_TO_REPORT(COUNT(1)) OVER() * 100 STATO_PERCENT
FROM LOG_BONIFICA
GROUP BY STATO
so you don't need to calculate total row count and ratio yourself.
所以你不需要自己计算总行数和比率。
