Percentage

Toad for Oracle & DevOps Toolkit
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

like this?

SELECT
crn,
grade,
grade_count,
SUM (grade_count) over (partition BY (crn)) AS sum_grade_count,
ROUND ( ( (grade_count) / (SUM (grade_count) over (partition BY (crn))) * 100), 2) AS percentage
FROM
(
SELECT
SHRTCKN_CRN AS CRN,
SHRTCKG_GRDE_CODE_FINAL AS GRADE,
COUNT (SHRTCKG_GRDE_CODE_FINAL) AS grade_count
FROM
SHRTCKN,
SHRTCKG
WHERE
SHRTCKN_TERM_CODE = ‘201710’ – :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
)
GROUP BY
crn,
grade,
grade_count
ORDER BY
1,
2,
3;