ratio_to_report

(8.0.26)

drop table tab1;
create table tab1(col1 int, col2 int);

insert into tab1 values(1,2900);
insert into tab1 values(2,2500);
insert into tab1 values(3,2600);
insert into tab1 values(4,3100);
insert into tab1 values(5,2800);

select * from tab1;

select col1,col2, 1.0 * col2 / NULLIF(SUM(col2) OVER(),0)
from tab1
order by col1,col2
;

(19c)

drop table tab1 purge;
create table tab1(col1 int, col2 int);

insert into tab1 values(1,2900);
insert into tab1 values(2,2500);
insert into tab1 values(3,2600);
insert into tab1 values(4,3100);
insert into tab1 values(5,2800);
commit;
select * from tab1;

select col1,col2,ratio_to_report(col2) over()
from tab1
order by col1,col2
;

select col1,col2, 1.0 * col2 / NULLIF(SUM(col2) OVER(),0)
from tab1
order by col1,col2
;

(14)

drop table tab1;
create table tab1(col1 int, col2 int);

insert into tab1 values(1,2900);
insert into tab1 values(2,2500);
insert into tab1 values(3,2600);
insert into tab1 values(4,3100);
insert into tab1 values(5,2800);

select * from tab1;

select col1,col2, 1.0 * col2 / NULLIF(SUM(col2) OVER(),0)
from tab1
order by col1,col2
;

 

 

(2019)

drop table tab1;
create table tab1(col1 int, col2 int);

insert into tab1 values(1,2900);
insert into tab1 values(2,2500);
insert into tab1 values(3,2600);
insert into tab1 values(4,3100);
insert into tab1 values(5,2800);

select * from tab1;

select col1,col2, 1.0 * col2 / NULLIF(SUM(col2) OVER(),0)
from tab1
order by col1,col2
;