Getting ORA-00979: not a GROUP BY expression error

Question

I keep getting an error when I am trying to execute this code on Oracle

SELECT id,
       Max(id),
       artist AS"Artist"
FROM   d_songs
WHERE  duration IN( '3 min', '6 min', '10 min' )
HAVING id < 50
GROUP  BY id;

What am I doing wrong? It is saying that this is not a group by expression.

ORA-00979: not a GROUP BY expression is the error its giving me — cory harms, Mar 28 '13 at 23:27
This is a staggeringly trivial syntax error. Next time you get such a problem please search for the ORA- code. Only ask a new question if none of the hundreds of hits you'll get solve your problem. — APC, Mar 29 '13 at 00:02

What have you tried · Accepted Answer · 2013-03-29T13:35:12.790

2

Edit:

After you're edit, see this from the documentation:

You attempted to execute a SELECT statement which contained a GROUP BY function such as MIN, MAX, SUM or COUNT. You attempted to execute an expression within the SELECT list which is not in the GROUP BY clause.

Put Group By before Having, and also include all fields (not the max) in the Group By Clause

SELECT id, max(id), artist as "Artist"
From d_songs
Where duration in('3 min', '6 min', '10 min')
where id < 50
Group by id, artist

You can view the docs for more information

edited Mar 29 '13 at 13:35

answered Mar 28 '13 at 23:23

What have you tried

11,018
4
31
45

Hmmm, I tried that too but it is still giving me the same error :( – cory harms Mar 28 '13 at 23:25
@coryharms you need to include all fields in the group by (not including the max) – What have you tried Mar 28 '13 at 23:27
Thank you! that was what I was doing wrong. Thanks again. I would mark as answer but I need 6 reputation lol. – cory harms Mar 28 '13 at 23:29
1

Vote up requires 15 reputation I guess... :( – cory harms Mar 28 '13 at 23:35
Why would the HAVING clause contain "id < 50"? Can you move that to the WHERE clause please? – David Aldridge Mar 29 '13 at 09:11
@DavidAldridge Sorry, I was just c+p the OP's code - completely missed that. – What have you tried Mar 29 '13 at 13:02
Ahem .. it'd be before the group by clause ;) – David Aldridge Mar 29 '13 at 13:09
-_- * I need to just quite while I'm ahead! – What have you tried Mar 29 '13 at 13:34

Getting ORA-00979: not a GROUP BY expression error

1 Answers1