SELECT STUDENT_NAME, SUBJECT, TYPE_OF_DISTRIBUTION, SUM(POINTS) * WEIGHT_IN_PERCENTAGE / (COUNT(POINTS) * 100) FROM assignments a JOIN distributions d ON a.TYPE_OF_DISTRIBUTION = d.ASSIGNMENT_CATEGORY GROUP BY SUBJECT, TYPE_OF_DISTRIBUTION, STUDENT_NAME ORDER BY STUDENT_NAME ASC;
ERROR at line 1:
ORA-00979: not a GROUP BY expression
Advertisement
Answer
You are getting this error for WEIGHT_IN_PERCENTAGE
. If WEIGHT_IN_PERCENTAGE
is same for every row in a group you can use (SUM(POINTS) * max(WEIGHT_IN_PERCENTAGE))
or you can SUM(POINTS*WEIGHT_IN_PERCENTAGE)
multiply it with POINTS
before sum:
SELECT STUDENT_NAME, SUBJECT, TYPE_OF_DISTRIBUTION, SUM(POINTS) * max(WEIGHT_IN_PERCENTAGE) / (COUNT(POINTS) * 100) FROM assignments a JOIN distributions d ON a.TYPE_OF_DISTRIBUTION = d.ASSIGNMENT_CATEGORY GROUP BY SUBJECT, TYPE_OF_DISTRIBUTION, STUDENT_NAME ORDER BY STUDENT_NAME ASC;
OR
SELECT STUDENT_NAME, SUBJECT, TYPE_OF_DISTRIBUTION, SUM(POINTS * WEIGHT_IN_PERCENTAGE) / (COUNT(POINTS) * 100) FROM assignments a JOIN distributions d ON a.TYPE_OF_DISTRIBUTION = d.ASSIGNMENT_CATEGORY GROUP BY SUBJECT, TYPE_OF_DISTRIBUTION, STUDENT_NAME ORDER BY STUDENT_NAME ASC;