Postgresql Calculations / AngularJS html table calculations

I’m working on a rather tricky situation. I have an angularJS application that generated a report card for student based on exams that student has taken. For an exam to be done, an exam type has to be set up. Think of this like the name of the exam, and this is a sample exam_types table;

exam_id | exam_name |
--------|-----------|
   1    | exam one  |
   2    | exam two  |
   3    | exam three|
   4    | last exam |
--------|-----------|

When a student has sat for the exams, my postgres query returns the data to be populated in the report card like so (notice that the last_exam is yet to be done);

subject_name| exam_id | exam_type | mark | out_of | grade |
------------|---------|-----------|------|--------|-------|
 ENGLISH    | xxxx    | exam_one  | 15   | 30     | C     |
 ENGLISH    | xxxx    | exam_two  | 15   | 30     | C     |
 ENGLISH    | xxxx    | exam_three| 20   | 40     | C     |
------------|---------|-----------|------|--------|-------|

And the report card will look like this (no problem up to this point)

--------|-----------------|-----------------|-----------------|-----------|
        |    EXAM ONE     |    EXAM TWO     |    EXAM THREE   |  OVERALL  |
--------|-----------------|-----------------|-----------------|-----------|
subject |mark|out_of|grade|mark|out_of|grade|mark|out_of|grade| avg| grade|
--------|----|------|-----|----|------|-----|----|------|-----|----|------|
ENGLISH | 15 | 30   | C   | 15 | 30   | C   | 20 | 40   | C   | 50 | C    |<--50 is a simple percentage of the sum of all the exams taken
--------|----|------|-----|----|------|-----|----|------|-----|----|------|

Now the problem comes in with the last_exam, the request is that all the exams done before the last_exam be converted to 30% and added to the last_exam which will be out of 70% totalling to 100%.

PROBLEM 1

Hence now suppose the student sits for the last_exam and the query returns this;

subject_name| exam_id | exam_type | mark | out_of | grade |
------------|---------|-----------|------|--------|-------|
 ENGLISH    | xxxx    | exam_one  | 15   | 30     | C     | <--this, plus
 ENGLISH    | xxxx    | exam_two  | 15   | 30     | C     | <--this, plus
 ENGLISH    | xxxx    | exam_three| 20   | 40     | C     | <--this adds up to 100 (then converted to 30% ie *0.3)
 ENGLISH    | xxxx    | last_exam | 50   | 70     | B+    | <--then added to this to make up 100
------------|---------|-----------|------|--------|-------|

Question – How do you set up a postgresql query to do a math function on all but the last row (last_exam for me) and then perform another math function with the result now on the last row? Is this possible in a single query?

PROBLEM 2

Now this is with the view. I’m using angularJS. The html table (report card) without the last_exam is shown above. But now with the existence of last_exam this is how it should look (the OVERALL columns);

--------|-----------------|-----------------|-----------------|-----------------|-----------|
        |    EXAM ONE     |    EXAM TWO     |    EXAM THREE   |    LAST EXAM    |  OVERALL  |
--------|-----------------|-----------------|-----------------|-----------------|-----------|
subject |mark|out_of|grade|mark|out_of|grade|mark|out_of|grade|mark|out_of|grade| avg| grade|
--------|----|------|-----|----|------|-----|----|------|-----|----|------|-----|----|------|
ENGLISH | 15 | 30   | C   | 15 | 30   | C   | 20 | 40   | C   | 50 | 100  | B+  | 65 | B    |
--------|----|------|-----|----|------|-----|----|------|-----|----|------|-----|----|------|

That is (exam_one) + (exam_two) + (exam_three) = answer * 0.3 = answer2 + last_exam

Question – Is there a way to dynamically populate this column with the proper data depending on whether last_exam has been done?

Source: AngularJS