Oracle:如何通过查询获取总数的百分比? [英] Oracle: how to get percent of total by a query?
本文介绍了Oracle:如何通过查询获取总数的百分比?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下查询.而且它返回的结果是非常不期望的:
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;
它为所有行返回100
it returns 100 for all rows
推荐答案
count(1)
在您的情况下等于sum(1)
.
尝试这样的事情:
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
更新:甚至使用ratio_to_report更简单
update: or even simplier with 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
这篇关于Oracle:如何通过查询获取总数的百分比?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文