CODEWITHSUNDEEP
mysqlsqljoin
Sioned Freer
Sioned Freer

Reputation: 1

Selection of the overall grade from one table with join between two tables

I'm trying to select columns from 2 tables and add them together if they are using the same secondary key. Here are my tables:

assignments table
assID  modID   userID  assName  grade  weighting
1       1       1      ass1      70      25
2       1       1      ass2      65      75
3       2       1      ass3      71      50
4       2       1      ass4      74      50


modules table
modID  SOUD  modName  
1      2326  server side
2      2345  OOP

I want to find the overall grade from each module by using SUM(Grade*(Weighting/100)) and adding together the results of columns with the same modID

My current code is:

SELECT SOUD, ModuleName, SUM(Grade*(Weighting/100)) 
FROM `assignments`, `modules` 
GROUP BY assignments.ModuleID

I've also tried

SELECT SOUD, ModuleName, SUM(Grade*(Weighting/100)) 
FROM `assignments`, `modules` 
GROUP BY assignments.ModuleID

but neither work

Is there any way I can do this?

Upvotes: 0

Views: 36

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35583

This is one of your queries:

SELECT SOUD
 , ModuleName
 , SUM(Grade * (Weighting / 100))
FROM `assignments`
 , `modules`
GROUP BY assignments.ModuleID

What that syntax does is MULTIPLY every rows in assignments withe EVERY row from modules (this is called a "Cartesian product"). So to fix this STOP using commas in the from clause.

SELECT
   m.SOUD
 , m.ModuleName
 , SUM(a.Grade * (a.Weighting / 100))
FROM `assignments` a
INNER JOIN`modules` m ON a.modID = m.modID  
GROUP BY a.ModuleID

Note also, that when you join tables it is VITAL to prefix either the table name or table alias with EVERY column reference.

One further caution, the query above "might work" in MySQL depending on server settings. MySQL has a very odd and non-standard way of dealing with group by so if you want a robust solution always include the columns of the select clause (that do NOT use an aggregate function) into the group by clause, like so:

SELECT
   m.SOUD
 , m.ModuleName
 , SUM(a.Grade * (a.Weighting / 100))
FROM `assignments` a
INNER JOIN`modules` m ON a.modID = m.modID  
GROUP BY
   m.SOUD
 , m.ModuleName

Upvotes: 1

Related Questions