Home » SQL & PL/SQL » SQL & PL/SQL » Percentage of occurrences of a row (Oracle 11g 11.2.0.2.0)
Percentage of occurrences of a row Wed, 17 May 2017 12:34
 Cirov Messages: 13Registered: 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 #662973 is a reply to message #662972] Wed, 17 May 2017 12:37
 joy_division Messages: 4800Registered: February 2005 Location: East Coast USA Senior Member
Search for RATIO_TO_REPORT.
Re: Percentage of occurrences of a row [message #662974 is a reply to message #662972] Wed, 17 May 2017 13:24
 Michel Cadot Messages: 65249Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

Michel Cadot wrote on Sun, 14 May 2017 13:12

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Re: Percentage of occurrences of a row [message #663006 is a reply to message #662972] Thu, 18 May 2017 07:24
 robfeist Messages: 2Registered: 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
Re: Percentage of occurrences of a row [message #663007 is a reply to message #663006] Thu, 18 May 2017 07:29
 BlackSwan Messages: 25743Registered: January 2009 Location: SoCal Senior Member
Welcome to this forum

OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Percentage of occurrences of a row [message #663056 is a reply to message #663006] Sat, 20 May 2017 03:53
 Cirov Messages: 13Registered: May 2017 Junior Member

Thanks for your help, it worked!
Re: Percentage of occurrences of a row [message #663057 is a reply to message #663056] Sat, 20 May 2017 03:55
 Michel Cadot Messages: 65249Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

Thanks for the feedback.
Next time, please, post the test case like robfeist did (but formatted as explained in the links BlackSwan posted).

 Previous Topic: listagg function in oracle Next Topic: Working of stored procedure
Goto Forum:

Current Time: Tue Dec 12 10:46:20 CST 2017

Total time taken to generate the page: 0.06511 seconds