Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

calculate the count and percentage of VARRAY

PAN KEVINMar 5 2020 — edited Mar 6 2020

I have a table with VARRAY field and want to calculate its count and percentage.

DATE_START         DATE_END           O3                       COUNT                                  PERCENTAGE                            

2005/1/1           2005/1/4           (1, 2, 3, 4, 5)                                                                             

2005/1/3           2005/1/11          (2, 3, 4, 5, 6)                                                                             

2005/1/10          2005/1/13          (3, 4, 5, 6, 7)                                                                             

2005/1/5           2005/1/8           (4, 5, 6, 7, 8)                                                                             

2005/1/7           2005/1/14          (5, 6, 7, 8, 9)                                                                             

I will calculate the count of how many elements in VARRAY O3 greater than or equal to 5 (A), and find out what percentage of A.

for example, the second-row show VARRAY O3 is (1, 2, 3, 4, 5). so A = 1,  percentage of A is 1/5 = 20%.

the last-row show VARRAY O3 is (5, 6, 7, 8,9). so A = 5,  percentage of A is 5/5 = 100%.

what I expected is

DATE_START         DATE_END           O3                                  COUNT                                  PERCENTAGE                            

2005/1/1           2005/1/4           (1, 2, 3, 4, 5)                    1                                             0.2            

2005/1/3           2005/1/11          (2, 3, 4, 5, 6)                    2                                             0.4      

2005/1/10          2005/1/13          (3, 4, 5, 6, 7)                    3                                             0.6    

2005/1/5           2005/1/8           (4, 5, 6, 7, 8)                    4                                             0.8   

2005/1/7           2005/1/14          (5, 6, 7, 8, 9)                    5                                             1.0  

thanks in advance

CREATE OR REPLACE TYPE NUMBER_VARRAY_5 AS VARRAY (5) OF NUMBER (8,2) NOT NULL;

create table TEST_3

(

  date_start DATE,

  date_end   DATE,

  o3         NUMBER_VARRAY_5

);

--

Insert into TEST_3

   (DATE_START, DATE_END, O3)

Values

   (TO_DATE('2005/1/1', 'YYYY/MM/DD'), TO_DATE('2005/1/4', 'YYYY/MM/DD'), NUMBER_VARRAY_5(1,2,3,4,5));

Insert into TEST_3

   (DATE_START, DATE_END, O3)

Values

   (TO_DATE('2005/1/3', 'YYYY/MM/DD'), TO_DATE('2005/1/11', 'YYYY/MM/DD'), NUMBER_VARRAY_5(2,3,4,5,6));

Insert into TEST_3

   (DATE_START, DATE_END, O3)

Values

   (TO_DATE('2005/1/10', 'YYYY/MM/DD'), TO_DATE('2005/1/13', 'YYYY/MM/DD'), NUMBER_VARRAY_5(3,4,5,6,7));

Insert into TEST_3

   (DATE_START, DATE_END, O3)

Values

   (TO_DATE('2005/1/5', 'YYYY/MM/DD'), TO_DATE('2005/1/8', 'YYYY/MM/DD'), NUMBER_VARRAY_5(4,5,6,7,8));

Insert into TEST_3

   (DATE_START, DATE_END, O3)

Values

   (TO_DATE('2005/1/7', 'YYYY/MM/DD'), TO_DATE('2005/1/14', 'YYYY/MM/DD'), NUMBER_VARRAY_5(5,6,7,8,9));

COMMIT;

This post has been answered by mathguy on Mar 5 2020
Jump to Answer

Comments

chonewell Nov 12 2024

My Oracle Cloud tenant, cloud account, and secure email have no issues. Why haven't I received my password reset email for Oracle Cloud? This is very strange, and our attempts have not been able to solve the problem. May I ask who I should turn to for help?

L. Fernigrini Nov 12 2024

If your account is a paid one, open a Support ticket.

If it is a Free Tier then you will have to rely on help from the community. Most probable cause that you did not receive the password reset email is that your account has been stolen and the email has been changed.

chonewell Nov 13 2024

Thank you for your reply!
But when I chatted with the online customer service, they told me that my Oracle Cloud tenant, account, and email were all fine. So, there shouldn't be a problem of theft.
I have a free account, but who can I contact on the forum? I can only post, but no one on the forum can view my account permissions, right. I am currently trying to reset MFA, I don't know if it works.
It's quite ridiculous that I have a free account and can't enjoy any services, but how can I become a paid user if I can't log in to my account.

1 - 3

Post Details

Added on Mar 5 2020
7 comments
318 views