Divide MAX score by Grade in different tables MySQL

Question

table a
no            name
2001           jon
2002           jonny
2003           mik
2004           mike
2005           mikey
2006           tom
2007           tomo
2008           tommy

table b
code       name      credits     courseCode
A2         JAVA        25           wer
A3         php         25           wer
A4         oracle      25           wer
B2         p.e         50           oth
B3         sport       50           oth
C2         r.e         25           rst
C3         science     25           rst
C4         networks    25           rst

table c
studentNumber     grade      coursecode
2003                68          A2
2003                72          A3   
2003                53          A4
2005                48          A2
2005                52          A3
2002                20          A2
2002                30          A3
2002                50          A4
2008                90          B2
2007                73          B2
2007                63          B3 





 SELECT a.num, a.Fname, 
   b.courseName, b.cMAXscore, b.cCode, c.stuGrade

FROM a
 INNER JOIN c
ON a.no = c.no
 INNER JOIN b
ON c.moduleCode = b.cCode
 INNER JOIN b
ON SUM(b.cMAXscore) / (c.stuGrade)
  AND b.cMAXscore = c.stug=Grade
GROUP BY  a.Fname, b.cMAXscore, b.cCode, b.courseName,c.stuGrade

"calculate and display every student name(a.Fname) and their ID number(a.num) along with their grade (c.grade) versus the coursse name(b.courseName) and the courses max score(b.cMAXscoure). "

I cant figure out how to divide the MAX by the grade, can someone help?

Answer 1

From the specification, it doesn't look like an aggregate function or a GROUP BY would be necessary. But the specification is ambiguous. There's no table definitions (beyond the unfortunate names and some column references).

Definitions of the tables, along with example data and an example of the desired resultset would go a long ways to removing the ambiguity.

Based on the join predicates in the OP query, I'd suggest something like this query, as a starting point:

SELECT a.Fname
     , a.num
     , c.grade
     , b.courseName
     , b.cMAXsource
  FROM a 
  JOIN c 
    ON c.no = a.no
  JOIN b
    ON b.cCode = c.moduleCode
 ORDER
    BY a.Fname
     , a.num
     , c.grade
     , b.courseName
     , b.cMAXsource

It seems like that would return the specified result (based on my interpretation of the vague specification.) If that's insufficient i.e. if that doesn't return the desired resultset, then in what way does the desired result differ from the result from this query?

(For more help with your question, I suggest you setup a sqlfiddle example with tables and example data. That will make it easier for someone to help you.)

FOLLOWUP

Based on the additional information provided in the question (table definitions and example data...

To get the maximum (highest) grade for a given course, you could use a query like this:

SELECT MAX(c.grade)
  FROM c
 WHERE c.coursecode = 'A2'

To get the highest grade for all courses:

SELECT c.coursecode
     , MAX(c.grade) AS max_grade
  FROM c
 GROUP BY c.coursecode
 ORDER BY c.coursecode 

To match the highest grade for each course to each student grade, use that previous query as an inline view in another query. Something like this:

SELECT g.studentNumber
     , g.grade
     , g.coursecode
     , h.coursecode
     , h.highest_grade
  FROM c g
  JOIN ( SELECT c.coursecode
              , MAX(c.grade) AS highest_grade
           FROM c
          GROUP BY c.coursecode
       ) h
    ON h.coursecode = g.coursecode

To perform a calculation, you can use an expression in the SELECT list of the outer query.

For example, to divide the value of one column by another, you can use the division operator:

SELECT g.studentNumber  AS student_number
     , g.grade          AS student_grade
     , g.coursecode     AS student_coursecode
     , h.coursecode 
     , h.highest_grade
     , g.grade / h.highest_grade AS `student_grade_divided_by_highest_grade`
  FROM c g
  JOIN ( SELECT c.coursecode
              , MAX(c.grade) AS highest_grade
           FROM c
          GROUP BY c.coursecode
       ) h
    ON h.coursecode = g.coursecode

If you want to also return the name of the student, you can perform a join operation to (the unfortunately named) table a. Assuming that studentnumber is UNIQUE in a :

  LEFT
  JOIN a
    ON a.studentnumber = c.studentnumber

And include a.Fname AS student_first_name in the SELECT list.

If you also need columns from table b, then join that table as well. Assuming that coursecode is UNIQUE in b:

  LEFT
  JOIN b
    ON b.coursecode = g.courscode

Then b.credits can be referenced in an expression in the SELECT list.

Beyond that, you need to be a little more explicit about what result should be returned by the query.

If you are after a "total overall grade" for a student, you'd need to specify how that result should be obtained.

Answer 2

The problem statement doesn't say anything about dividing by the max, I think you're misunderstanding it.

You need to write a subquery that gets the maximum score for each class, using MAX and GROUP BY. You can then join this with the other tables.

SELECT s.name AS student_name, c.name AS course_name, g.grade, m.max_grade
FROM student AS s
JOIN grade AS g ON s.no = g.studentNumber
JOIN course AS c ON c.code = g.courseCode
JOIN (SELECT courseCode, MAX(grade) AS max_grade
      FROM grade
      GROUP BY courseCode) AS m
    ON m.courseCode = c.courseCode

If you did need to divide the grade by the maximum, you can use g.grade/m.max_grade.

Answer 3

Without knowing table definations it is very hard to provide solution to your problem.

Here is my version of what you are trying to look for:

DECLARE @Student TABLE
(StudentID INT IDENTITY,
 FirstName VARCHAR(255),
 LastName  VARCHAR(255)
);

DECLARE @Course TABLE
(CourseID   INT IDENTITY,
 CourseCode VARCHAR(25),
 CourseName VARCHAR(255),
 MaxScore   INT
);

DECLARE @Grade TABLE
(ID        INT IDENTITY,
 CourseID  INT,
 StudentID INT,
 Score     INT
);
--Student

insert into @Student(FirstName, LastName)   
values ('Test', 'B')


insert into @Student(FirstName, LastName)   
values ('Test123', 'K')


--Course
insert into @Course(CourseCode, CourseName, MaxScore)   
values ('MAT101', 'MATH',100.00)    

insert into @Course(CourseCode, CourseName, MaxScore)   
values ('ENG101', 'ENGLISH',100.00)


--Grade 
insert into @Grade(CourseID, StudentID, Score)  
values (1, 1,93)    

insert into @Grade(CourseID, StudentID, Score)  
values (1, 1,65)

insert into @Grade(CourseID, StudentID, Score)  
values (1, 1,100)


insert into @Grade(CourseID, StudentID, Score)  
values (2, 1,100)   

insert into @Grade(CourseID, StudentID, Score)  
values (2, 1,69)

insert into @Grade(CourseID, StudentID, Score)  
values (2, 1,95)


insert into @Grade(CourseID, StudentID, Score)  
values (1, 2,100)   

insert into @Grade(CourseID, StudentID, Score)  
values (1, 2,65)

insert into @Grade(CourseID, StudentID, Score)  
values (1, 2,100)


insert into @Grade(CourseID, StudentID, Score)  
values (2, 2,100)   

insert into @Grade(CourseID, StudentID, Score)  
values (2, 2,88)

insert into @Grade(CourseID, StudentID, Score)  
values (2, 2,96)


SELECT a.StudentID,
       a.FirstName,
       a.LastName,
       c.CourseCode,
       SUM(b.Score) AS 'StudentScore',
       SUM(c.MaxScore) AS 'MaxCourseScore',
       SUM(CAST(b.Score AS DECIMAL(5, 2))) / SUM(CAST(c.MaxScore AS DECIMAL(5, 2))) AS 'Grade'
FROM @Student a
     INNER JOIN @Grade b ON a.StudentID = b.StudentID
     INNER JOIN @Course c ON c.CourseID = b.CourseID
GROUP BY a.StudentID,
         a.FirstName,
         a.LastName,
         c.CourseCode;