Can you help me to correct this query in sql-CodePudding

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

CodePudding user response：

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;

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;