Percentage

Toad for Oracle
1

Greetings!

I have a query that gives me correct answer but I can’t figure out how to get percentage. What I have tried so far is as follows:

…

select SHRTCKN_CRN CRN, count (distinct (SHRTCKN_PIDM)) PIDM, SHRTCKG_GRDE_CODE_FINAL GR /*round((((count(SHRTCKG_GRDE_CODE_FINAL))/count (distinct (SHRTCKN_PIDM)))100), 2) Perc/

from SHRTCKN, SHRTCKG
where SHRTCKN_TERM_CODE =: p_term
and SHRTCKN_PIDM = SHRTCKG_PIDM
and SHRTCKN_TERM_CODE = SHRTCKG_TERM_CODE
and SHRTCKN_SEQ_NO = SHRTCKG_TCKN_SEQ_NO
and SHRTCKG_GRDE_CODE_FINAL in (‘A’, ‘B+’, ‘B’, ‘C+’, ‘C’, ‘D+’, ‘D’, ‘F’, ‘FA’, ‘GR’, ‘W’, ‘WF’, ‘I’)
group by SHRTCKN_CRN, SHRTCKG_GRDE_CODE_FINAL

…

Result of my query is as follows:

CRN PIDM GR

20001 4 A
20001 4 B
20001 4 B+
20001 2 C
20001 7 C+
20001 7 D
20001 2 D+
20001 3 F
20002 8 A
20002 14 B
20002 11 B+
20002 2 C
20002 7 C+
20002 2 D+
20002 1 W

…

I am trying to find percentage of GR per CRN i.e count of PIDM per GR per CRN divided by total count of PIDM per CRN. As you will notice from the commented area of my select statement, my attempts have not been successful.

Any assistance provided would be appreciated.

2

Your question is fairly generic, so you'd probably get a better response in the Oracle Community at http://www.toadworld.com/platforms/oracle/f/53

But if you'd like a starting point, check out Oracle Analytical Functions, like SUM(PIDM) OVER (PARTITION BY CRN ORDER BY CRN)

Good Luck!
Rich

3

Hi Rich,

Thank you for responding to my post and suggesting a starting point. I tried your suggestion and it worked for first part, i.e. I was able to get total count of PIDM per CRN but I can’t seem to get count of individual grades per CRN. Any additional advice would be appreciated.

I have also followed up on your suggestion and posted it on Oracle community.

Jolly