CODEWITHSUNDEEP
sql
redhat01
redhat01

Reputation: 135

Why "not a GROUP BY expression"?

I have some problems with understanding the "not a GROUP BY expression" error. As you can see bellow, I have 2 tables: students and grade_students. I need to create a select that will print: students.name, students.year, students.code and the avg of his grades (grade_students.grade).

If I create the SELECT to print me only name and avg is just fine:

SELECT 
    students.name, avg(grade_students.grade) 
FROM 
    students
INNER JOIN 
    grade_students ON students.code = grade_students.code
GROUP BY 
    students.name 
ORDER BY 
    avg_gr;

BUT when I ask for year and code as bellow I got the "not a GROUP BY expression"

SELECT 
    students.name, avg(grade_students.grade) AS avg_gr, 
    students.year, students.code 
FROM 
    students
INNER JOIN 
    grade_students ON students.code= grade_students.code
GROUP BY 
    students.name 
ORDER BY 
    avg_gr;

Please give me a hint to figure out what is wrong here.

PS. I'm not doing my homework on stackoverflow.com but I admit, I'm preparing myself for an exam :)

EDIT:

Name    Null     Type      -- students    
------- -------- ------------ 
CODE NOT NULL NUMBER(4)    
NAME VARCHAR2(20) 
SECOND_NAME VARCHAR2(20) 
YEAR NUMBER(1)  

Name Null Type          -- grade_students
---- ---- ----------- 
CODE NUMBER(4) REFERENCES students ( code),   
NOTA      NUMBER(5,2)

Upvotes: 0

Views: 349

Answers (3)

Selay
Selay

Reputation: 6464

SELECT s.name, avg(g.grade) AS avg_gr, s.year, s.code FROM students s INNER JOIN grade_students g ON s.code= g.code GROUP BY s.name, s.year, s.code ORDER BY avg_gr;

This problem is discussed here: Oracle: NOT A GROUP BY EXPRESSION ERROR

Upvotes: 0

user743382
user743382

Reputation:

What if two students have the same name, but different years? You say you want to group by student name, so you only want to return a single record, but you also want the two years, when your result row only has room for one year.

In my opinion, it doesn't make sense to use GROUP BY at the top level, here. You want the average student grade (per student code)? Then group just for that.

SELECT students.name, avg_grade_students.avg_grade
FROM students
INNER JOIN (
    SELECT code, AVG(grade) AS avg_grade
    FROM grade_students
    GROUP BY code
) AS avg_grade_students
ON students.code = avg_grade_students.code
ORDER BY avg_grade_students.avg_grade;

Here, you can add any column from students you want without any problems.

(Precise syntax depends slightly on the database used.)

Upvotes: 0

TomT
TomT

Reputation: 971

You either need to add students.year and students.code to group by clause:

SELECT students.name, avg(grade_students.grade) AS avg_gr, students.year, students.code FROM students
 INNER JOIN grade_students ON students.code= grade_studenti.code
 GROUP BY students.name, students.year, students.code
 ORDER BY avg_gr;

Or you need to aggregate those fields:

SELECT students.name, avg(grade_students.grade) AS avg_gr, MAX(students.year) AS max_std_year, MAX(students.code) AS max_std_code FROM students
 INNER JOIN grade_students ON students.code= grade_studenti.code
 GROUP BY students.name ORDER BY avg_gr;

Upvotes: 3

Related Questions