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?
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 ?
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.