Menu
I have the following query and its result is very unexpected to me:
select stato, (count(1) *100) / sum(1)
from LOG_BONIFICA
group by stato;
it returns 100 for all rows. Should it be different ?
692
There is no built-in operator that calculates percentages in SQL Server. You have to rely on basic arithmetic operations i.e. (number1/number2 x 100) to find percentages in SQL Server.
In mathematics, a percentage is a number or ratio that can be expressed as a fraction of 100. If we have to calculate percent of a number, divide the number by the whole and multiply by 100.
How do I add a percent symbol to a SQL query? MySQL. In MySQL, we can use the CONCAT() function to concatenate the number and the percent sign: SELECT CONCAT(3.75, '%'); SQL Server.
count(1) is equal to sum(1) in your case.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
