Multiplying fields from separate columns which have the same ID in SQL?

Question

I have two tables which are joined by an ID...

table 1

- Assessment ID 
- Module ID 
- Assessment Weighting

table 2

- ID 
- AssessmentID
- ModuleID
- UserID
- MarkFrom100 

An assessment can have many students taking the assessment. For example A module has two assessments, one worth 60% and the other worth 40%. in table 2, I want to take the weighting value from table 1 and multiply it against the mark from 100.

SELECT * FROM Assessment, ModuleAssessmentUser WHERE 
INNER JOIN moduleassementuser.assessmentID on Assessment.assessmentID
MULTIPLY AssessmentWeighting BY MarkFrom100 AS finalmark
UserID = 1

I know this is way off, but I really don't know how else to go about it.

My SQL knowledge is limited, so any help is appreciated!

Answer 1

You may use a SUM function in your query which will sum all the data of a certain group in a sub query wich will allow you to multiply the sum to the weight

sub query :

SELECT ModuleID, AssessmentID, UserID, SUM(MarkFrom100) as Total
    FROM Table_2
    GROUP BY ModuleID

Then use this sub query as a table in a main query :

SELECT T1.Assessment_ID, T1.ModuleID, Q1.UserID (Q1.Total * T1.Assessment_Weighting) as FinalMark
    FROM (SELECT ModuleID, UserID, SUM(MarkFrom100) as Total
              FROM Table_2
              GROUP BY ModuleID) AS Q1
    INNER JOIN Table_1 as T1 on T1.ModuleID = Q1.ModuleID
--  WHERE T1.ModuleID = 2  -- a particular module ID
    GROUP BY ModuleID;

Note that the WHERE statement is in comment. If you want the whole data, remove it, if you want a particular data, use it ^^

---

NOTE :

I don't have your database, so it may need some tweeks, but the main idea is there