Calculate sum of one row and divide by sum of another row. Oracle view/query

Question

This is my first question on here so bear with me. I have two tables in my Oracle database as following:

modules with fields:

• module_code eg. INF211
• module_title eg. Information technology
• credits eg.20

module_progress with fields:

• student_id eg. STU1
• module_code eg. INF211
• module_year eg. 1
• module_percent eg. 65

Each student takes 5 modules a year.

So this is what I want to put this all in one query/view if possible:

1. Find sum of module percents for a particular student
2. Find sum of all credits for each module with regards to their module percents.
3. Divide sum of module percents by sum of credits and multiply by 100 to give me an average grade.

Can this be done?

Answer 1

SELECT  student_id,
        SUM(credits * module_percent) / SUM(credits) * 100.0
FROM    module_progress mp
JOIN    modules m
ON      m.module_code = mp.module_code
GROUP BY
        student_id