sqloracle-databasecount

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

  • 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;