Oracle SQL code syntax standard, Error "not a single-group group function"

Question

I have a block of Oracle SQL code that does work. I figured out I cannot get the cert column and the max(run_time) at the same time.

select
  cert,
  max(run_time)
from
  bond_films
where title in
(
'Casino Royale','On Her Majesty_s Secret Service','Licence to Kill'
);

I was reading a SQL reference and it has the following syntax, but I thought my code matches the syntax?

SELECT [column,] group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column] ;

Can someone explain this to me or suggest a source which says I cannot write a code like I did?

Michael Berkowski · Accepted Answer · 2011-08-30 13:40:55Z

2

You need a GROUP BY clause because you have included the cert column. If you had only done SELECT MAX(run_time) it would not be necessary to supply a GROUP BY since you are selecting the maximum value of all rows. But, since you have requested another column (cert), Oracle expects that you want to retrieve the max value for each different cert.

select
  cert,
  max(run_time)
from
  bond_films
where title in ('Casino Royale','On Her Majesty_s Secret Service','Licence to Kill')
GROUP BY cert;

answered Aug 30, 2011 at 13:40

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dean Over a year ago

Michael, Thank you for your advise. Can you tell me where to find relevent syntax so that I know I have to use GROUP BY in this case?

Michael Berkowski Over a year ago

@Dean I'm sorry I can't find the relevant Oracle docs, and I only know it from experience. MySQL will not require GROUP BY in this instance and would group & return all rows, but Oracle & others require it.

Collectives™ on Stack Overflow

Oracle SQL code syntax standard, Error "not a single-group group function"

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related