I need to count the number of students who were registered in a given course over an academic year.
The following SELECT
query returns a column of course numbers and a column of the academic year.
SELECT COURSE_SUBJECT_CODE || ' ' || COURSE_NUMBER AS COURSE,
CASE SUBSTR(TERM_CODE_KEY, 5, 2)
WHEN '01' THEN TO_CHAR(TO_NUMBER(SUBSTR(TERM_CODE_KEY, 1, 4))-1)
WHEN '05' THEN TO_CHAR(TO_NUMBER(SUBSTR(TERM_CODE_KEY, 1, 4))-1)
ELSE SUBSTR(TERM_CODE_KEY, 1, 4)
END AS ACAD_YR
FROM DWH.SR_COURSES_FAC
WHERE (COURSE_SUBJECT_CODE = 'BMDE')
ORDER BY COURSE, ACAD_YR;
For example:
COURSE ACAD_YR
BMDE 1 2024
BMDE 1 2024
BMDE 2 2024
BMDE 2 2025
I would like the following result:
COURSE ACAD_YR NUM_STUDENTS
BMDE 1 2024 2
BMDE 2 2024 1
BMDE 2 2025 1
When I modify the query as follows aggregate counting:
SELECT COURSE_SUBJECT_CODE || ' ' || COURSE_NUMBER AS COURSE,
CASE SUBSTR(TERM_CODE_KEY, 5, 2)
WHEN '01' THEN TO_CHAR(TO_NUMBER(SUBSTR(TERM_CODE_KEY, 1, 4))-1)
WHEN '05' THEN TO_CHAR(TO_NUMBER(SUBSTR(TERM_CODE_KEY, 1, 4))-1)
ELSE SUBSTR(TERM_CODE_KEY, 1, 4)
END AS ACAD_YR,
COUNT(*) AS NUM_STUDENTS
FROM DWH.SR_COURSES_FAC
WHERE (COURSE_SUBJECT_CODE = 'BMDE')
GROUP BY COURSE, ACAD_YR
ORDER BY COURSE, ACAD_YR;
I get the following error: ORA-00904: "ACAD_YR": invalid identifier
flagged on the GROUP BY
line. I don't understand why.
I'd go with a derived table (subquery in the FROM clause), to avoid having to repeat the expressions:
SELECT COURSE, ACAD_YR, COUNT(*) AS NUM_STUDENTS
FROM (
SELECT COURSE_SUBJECT_CODE || ' ' || COURSE_NUMBER AS COURSE,
CASE SUBSTR(TERM_CODE_KEY, 5, 2)
WHEN '01' THEN TO_CHAR(TO_NUMBER(SUBSTR(TERM_CODE_KEY, 1, 4))-1)
WHEN '05' THEN TO_CHAR(TO_NUMBER(SUBSTR(TERM_CODE_KEY, 1, 4))-1)
ELSE SUBSTR(TERM_CODE_KEY, 1, 4)
END AS ACAD_YR
FROM DWH.SR_COURSES_FAC
WHERE (COURSE_SUBJECT_CODE = 'BMDE')
) dt
GROUP BY COURSE, ACAD_YR
ORDER BY COURSE, ACAD_YR;