2

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.

Improve this question
0

2 Answers 2

Reset to default
5

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

LOL! I never would have guessed. And I wasted so much time reading into the count function! At least the error makes sense now. Thanks again!
You can ask AI, it answers such questions ;)
@Jonas Metzler I did ask AI. But I didn't explain the problem properly. But I am guessing that you mean that I could have provided the code and asked it for the solution. I never thought of doing that. Thanks for the tip.
Yes, you can provide the query and ask why it can't be executed. We should be very careful with AI, but for such simple tasks, it is great.
2

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;
Improve this answer

1 Comment

Very nice! Thanks to everybody for their input!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.