Reputation: 43
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
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
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