CODEWITHSUNDEEP
mysqlsql
Totoy Bibo
Totoy Bibo

Reputation: 43

How to get the average of grades in mysql

I have a problem. I don't know how to explain it in words but please see the code below and the result.

SELECT STUDENT_ID, FNAME, LNAME, MNAME, SUBJECT, GRADE, AVG(GRADE)
AS AVG_GRADE FROM V_STUDENT_GRADES WHERE SCHOOL_YEAR_ID = 2 AND LEVEL_ID = 7
AND PERIOD_ID =1 AND SUBJECT_ID=SUBJECT_ID 
GROUP BY STUDENT_ID, SUBJECT
ORDER BY LNAME, FNAME DESC;

Result:

+------------+------------------------+-------------+--------------------+-------------+-------+-----------+
| STUDENT_ID | FNAME                  | LNAME       | MNAME              | SUBJECT     | GRADE | AVG_GRADE |
+------------+------------------------+-------------+--------------------+-------------+-------+-----------+
|         12 | Roderick               | Almero      | A                  | CIVIL 111   |    75 |        75 |
|         12 | Roderick               | Almero      | A                  | ELECTIVE 1  |    74 |        74 |
|         12 | Roderick               | Almero      | A                  | BIOLOGY     |    87 |        87 |
|         12 | Roderick               | Almero      | A                  | CRIM 111    |    79 |        79 |
|         12 | Roderick               | Almero      | A                  | math 100    |   100 |       100 |
|         12 | Roderick               | Almero      | A                  | CONSTI 222  |    96 |        96 |
|         12 | Roderick               | Almero      | A                  | MAPEH       |    92 |        92 |
|         12 | Roderick               | Almero      | A                  | COMPUTER    |    82 |        82 |
|         12 | Roderick               | Almero      | A                  | ENGLISH 222 |   100 |       100 |
|         13 | Jasima                 | Alonto      | D                  | COMPUTER    |    83 |        83 |
|         13 | Jasima                 | Alonto      | D                  | ENGLISH 222 |    82 |        82 |
|         13 | Jasima                 | Alonto      | D                  | CIVIL 111   |    93 |        93 |
|         13 | Jasima                 | Alonto      | D                  | ELECTIVE 1  |    79 |        79 |
|         13 | Jasima                 | Alonto      | D                  | BIOLOGY     |    87 |        87 |
|         13 | Jasima                 | Alonto      | D                  | CRIM 111    |    72 |        72 |
|         13 | Jasima                 | Alonto      | D                  | math 100    |   100 |       100 |
|         13 | Jasima                 | Alonto      | D                  | CONSTI 222  |    94 |        94 |
|         13 | Jasima                 | Alonto      | D                  | MAPEH       |    98 |        98 |
+----------------------------------------------------------------------------------------------------------+

Expected: 

+------------+------------------------+-------------+--------------------+-------------+-------+--------------------+
| STUDENT_ID | FNAME                  | LNAME       | MNAME              | SUBJECT     | GRADE | AVG_GRADE          |
+------------+------------------------+-------------+--------------------+-------------+-------+--------------------+
|         12 | Roderick               | Almero      | A                  | CIVIL 111   |    75 |  87.22222222222223 |
|         12 | Roderick               | Almero      | A                  | ELECTIVE 1  |    74 |  87.22222222222223 |
|         12 | Roderick               | Almero      | A                  | BIOLOGY     |    87 |  87.22222222222223 |
|         12 | Roderick               | Almero      | A                  | CRIM 111    |    79 |  87.22222222222223 |
|         12 | Roderick               | Almero      | A                  | math 100    |   100 |  87.22222222222223 |
|         12 | Roderick               | Almero      | A                  | CONSTI 222  |    96 |  87.22222222222223 |
|         12 | Roderick               | Almero      | A                  | MAPEH       |    92 |  87.22222222222223 |
|         12 | Roderick               | Almero      | A                  | COMPUTER    |    82 |  87.22222222222223 |
|         12 | Roderick               | Almero      | A                  | ENGLISH 222 |   100 |  87.22222222222223 |
|         13 | Jasima                 | Alonto      | D                  | COMPUTER    |    83 |  87.55555555555556 |
|         13 | Jasima                 | Alonto      | D                  | ENGLISH 222 |    82 |  87.55555555555556 |
|         13 | Jasima                 | Alonto      | D                  | CIVIL 111   |    93 |  87.55555555555556 |
|         13 | Jasima                 | Alonto      | D                  | ELECTIVE 1  |    79 |  87.55555555555556 |
|         13 | Jasima                 | Alonto      | D                  | BIOLOGY     |    87 |  87.55555555555556 |
|         13 | Jasima                 | Alonto      | D                  | CRIM 111    |    72 |  87.55555555555556 |
|         13 | Jasima                 | Alonto      | D                  | math 100    |   100 |  87.55555555555556 |
|         13 | Jasima                 | Alonto      | D                  | CONSTI 222  |    94 |  87.55555555555556 |
|         13 | Jasima                 | Alonto      | D                  | MAPEH       |    98 |  87.55555555555556 |
+-------------------------------------------------------------------------------------------------------------------+

I want to display the average of the student in every row that has the same student id.

Please help. Thanks very much.

Upvotes: 2

Views: 609

Answers (2)

Jaylen
Jaylen

Reputation: 40381

Try this ` SELECT STUDENT_ID, FNAME, LNAME, MNAME, SUBJECT, GRADE, t.AVG_GRADE FROM V_STUDENT_GRADES as g Inner join ( SELECT STUDENT_ID, AVG(GRADE) AS AVG_GRADE FROM V_STUDENT_GRADES WHERE SCHOOL_YEAR_ID = 2 AND LEVEL_ID = 7 AND PERIOD_ID =1 AND SUBJECT_ID=SUBJECT_ID GROUP BY STUDENT_ID

) as t on t.STUDENT_ID = g.STUDENT_ID WHERE SCHOOL_YEAR_ID = 2 AND LEVEL_ID = 7 AND PERIOD_ID =1 AND SUBJECT_ID=SUBJECT_ID GROUP BY g.STUDENT_ID, SUBJECT ORDER BY LNAME, FNAME DESC;`

Upvotes: 0

Pantamtuy
Pantamtuy

Reputation: 243

TRY MO TO TROPA....! 

SELECT 
    STUDENT_ID, 
    FNAME, 
    LNAME, 
    MNAME, 
    SUBJECT, 
    GRADE, 
    (SELECT AVG(GRADE) FROM V_STUDENT_GRADES WHERE STUDENT_ID = vsg.STUDENT_ID) AS AVG_GRADE
    FROM V_STUDENT_GRADES vsg
    WHERE SCHOOL_YEAR_ID = 2 AND LEVEL_ID = 7
        AND PERIOD_ID =1 AND SUBJECT_ID=SUBJECT_ID 
        GROUP BY STUDENT_ID, SUBJECT
        ORDER BY LNAME, FNAME DESC;

Upvotes: 1

Related Questions