Marks by semester and term

Need to change code. Most students take 4 courses per reporting period for a total of 8 per year.. However grade 10s take 9 courses per year. Originally wrote query based on course section to determine 'bucket' to go into. Course sections would be 1 thru 4.

Now, for grade 10s they want to see 5 courses for report period 5 and 4 for report period 2M.
Not sure how to change when not checking for course section.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Based on your sql your bucketing depends on class_meetings.block .
I would expect a block  '5' for the extra course and a like extension to your  query
      case when (class_meetings.semester = '2' and class_meetings.block = '5')
             then class_meetings.CLASS_CODE || class_meetings.block END as SEM1BLOCK9,
      case when class_meetings.semester = '2' and substr(class_meetings.block,1,1) = '5'
                   and fs_secondary_course_achieve.course_code = student_program_class_tracks.course_code
             then fs_secondary_course_achieve.mark  END as mark9
You say you wrote the original query : then my expected extension  probably is not the solution.
You wil have to give more information on the registration of your courses in the different tables.
And the expected results.
For example : from your averages.jpg i can't see what row is related to the 5th course.
guinnieAuthor Commented:
The class_meetings.block values range from 1 thru 4. There isn't a 5. So the difficulty is to count the number of courses a student is taking during each semester. All grades will take max of 8 per year but grade 10's will take 9. The breakdown for grade 10's is still 4 per semester but in total 9. But 2 of the courses are 1/2 credits. That's how they get 9 different courses:
7 full credits and 2 half credits.
That's why I can't use block value in my new code. Does that help explain ?
If there are 2 courses within  the same class_meetings.block   each get the half credits ?
And what is the reason for difference checking on class_meetings.block = '4'
and substr(class_meetings.block,1,1) = '4'.  Are there more values then 1,2,3,4  ?
guinnieAuthor Commented:

  The point of my first comment was that I needed to rewrite code to eliminate checking the block for values.
My goal is to just get courses for each student for each semester.  My first code worked until I learnded grade 10 students
took 9 courses during the school year. So instead of using the block which divided classes by report period with 4 different blocks per semester, I need to reconstruct.
I need to reconstuct : that clear , but what do you want ?  Stil 4 differents blocks per semester ?  If yes ,  how do you recognize the block a course belongs to , how to recognize the courses that should have 1/2 credits?
Could you provide the courses for a student that took 9 courses a year and the output you desire ?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.