CODEWITHSUNDEEP
sql
Saad Jatt
Saad Jatt

Reputation: 1

facing this error ORA-00979: not a GROUP BY expression whats my mistake?

Here is my SQL query:

select f_name, avg(age) 
from students 
group by dept_id 
having avg(age) > age;

Why does this result in an error I mentioned in the question title?

Upvotes: 0

Views: 50

Answers (3)

Tom O.
Tom O.

Reputation: 5941

You can only GROUP BY a column if it is included in the SELECT statement. Also, if you're using a GROUP BY clause, you need to include all non-aggregate function columns in the GROUP BY.

Try:

select f_name, avg(age), dept_id
from students 
group by f_name, dept_id 
--not sure about this next line either
--having avg(age) > age;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You have f_name in the select, but are aggregating by department.

Your having clause mixes aggregations and non-aggregations.

Presumably, you want something like this:

select s.*
from (select s.*, avg(s.age) over (partition by s.dept_id) as avg_age
      from students s
     ) s 
where s.age > avg_age;

Upvotes: 0

lpp
lpp

Reputation: 26

f_name is not an aggregate on the group by. Try for instance min(f_name).

not sure if "having avg(age) > age;" will work as expected.

Upvotes: 0

Related Questions