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

Question

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?

Note that there are options to upvote and 'accept' answers. – Strawberry Nov 21 '17 at 00:41 — Strawberry, Nov 21 '17 at 00:41

Paul Maxwell · Answer 1 · 2017-11-20T23:35:15.320

1

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

edited Nov 20 '17 at 23:35

answered Nov 20 '17 at 23:28

Paul Maxwell

33,002
3
32
51

yeah i got that, how do i fix it – Sioned Freer Nov 20 '17 at 23:30
Thanks!! u are a genius – Sioned Freer Nov 20 '17 at 23:33
please learn how to properly join tables. "**commas between table names are dangerous**" and you should `avoid them like a plague!` – Paul Maxwell Nov 20 '17 at 23:37
You may find this helpful: [SQL JOIN and different types of JOINs](https://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins) – Paul Maxwell Nov 20 '17 at 23:42

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

1 Answers1