Oracle Group By – How to Group Joined Tables by Average

join;oracle

I'm trying to build a query selecting two columns (category types) from one table JOINED to another table-column displaying average grades as percentage number-types; here is my first attempt:

SELECT gt.grade_type_code,
       gt.description,
       gg.numeric_grade
FROM student.grade_type gt
JOIN student.grade gg ON gt.grade_type_code = gg.GRADE_TYPE_CODE;

Here is a screenshot of output for clarification:

enter image description here

My second attempt is closer to the expected result, sans "DESCRIPTION" column, necessitating the JOIN clause; I've also included the query and output below:

SELECT grade_type_code,
       count(grade_type_code),
       Round(avg(numeric_grade),2)
FROM student.grade
GROUP BY grade_type_code
ORDER BY GRADE_TYPE_CODE;

enter image description here

Lastly, here is my most recent attempt to combine the above logic:

SELECT gt.grade_type_code,
       gt.description,
       round(avg(gg.numeric_grade),2)
FROM student.grade_type gt
JOIN student.grade gg ON gt.grade_type_code = gg.GRADE_TYPE_CODE
GROUP BY gt.grade_type_code
ORDER BY gt.grade_type_code;

And the error message:

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:
Error at Line: 1 Column: 28

So essentially, I'm attempting to display the average NUMERIC_GRADE for the correspondingly grouped category types (i.e. Participation = avg percentage) but my GROUP_BY attempts invariably return errors (not sure why). I believe my logic is directionally correct, albeit flawed; could someone please advise? Thank you!

Best Answer

ORA-00979 not a GROUP BY expression

Cause: The GROUP BY clause does not contain all the expressions in the SELECT clause. SELECT expressions that are not included in a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY clause.
Action: Include in the GROUP BY clause all SELECT expressions that are not group function arguments.

Gt.description column is not listed in the group by clause. Columns not participating in group function should be listed in group by clause.

SELECT gt.grade_type_code,
       gt.description,
       round(avg(gg.numeric_grade),2) AS "Average Grade"
FROM student.grade_type gt
JOIN student.grade gg ON gt.grade_type_code = gg.GRADE_TYPE_CODE
GROUP BY gt.grade_type_code,
         gt.description
ORDER BY gt.grade_type_code;
Related Question