Percentage of occurrences of a row [message #662972] |
Wed, 17 May 2017 12:34 |
|
Cirov
Messages: 13 Registered: May 2017
|
Junior Member |
|
|
Hi,
I have the following table:
DESC DESC2
A X
B Y
A X
A X
B Z
A X
B W
A Y
I need a query that would result in the below:
DESC DESC2 RESULT
A X 0.5
The "RESULT" column is the amount of occurrences of the pair (A,X), 4 in a total of 8 rows. Is there a way to achieve that?
|
|
|
|
|
Re: Percentage of occurrences of a row [message #663006 is a reply to message #662972] |
Thu, 18 May 2017 07:24 |
|
robfeist
Messages: 2 Registered: May 2017 Location: Pennsylvania
|
Junior Member |
|
|
create table sample (desc_char char, desc2_char char);
insert into sample values ('A','X');
insert into sample values ('B','Y');
insert into sample values ('A','X');
insert into sample values ('A','X');
insert into sample values ('B','Z');
insert into sample values ('A','X');
insert into sample values ('B','W');
insert into sample values ('A','Y');
select desc_char, desc2_char, ratio_to_report(count(1)) over () ratio_result
from sample
group by desc_char, desc2_char;
output:
DESC_CHAR DESC2_CHAR RATIO_RESULT
B Y .125
B Z .125
A Y .125
B W .125
A X .5
|
|
|
|
|
|