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 [message #662972] Wed, 17 May 2017 12:34 Go to next message
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 #662973 is a reply to message #662972] Wed, 17 May 2017 12:37 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: 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 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your previous topic:

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 Go to previous messageGo to next message
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
Re: Percentage of occurrences of a row [message #663007 is a reply to message #663006] Thu, 18 May 2017 07:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
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 Go to previous messageGo to next message
Cirov
Messages: 13
Registered: 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 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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: Thu Mar 28 03:53:39 CDT 2024