Invalid identifier when using aggregate count(*)

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.

Solution

You need to repeat the expression in the GROUP BY clause, you can't reference it. Oracle doesn’t let you use column aliases unless wrapped in another SELECT, ORDER BY, or HAVING.

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_SUBJECT_CODE || ' ' || COURSE_NUMBER,
    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
ORDER BY COURSE, ACAD_YR;