ORA-00937: non-group group function

Question

SELECT MIN(retail) FROM books WHERE category = 'COMPUTER' 

works fine, but when I include the header in select like:

 SELECT MIN(retail), title FROM books WHERE category = 'COMPUTER' 

this is not true. What for? How to make it work?

Answer 1

Rhys answer is correct if that is what you mean, but you might want title (s), where retail=MIN(retail) , and this wording tells you how to get this answer:

 SELECT title, retail FROM books WHERE category = 'COMPUTER' AND retail = (SELECT MIN(retail) FROM books WHERE category = 'COMPUTER') 

To reduce duplication, you can use the WITH clause (if you are using the latest version of SQL):

 ;WITH ComputerBooks AS ( SELECT title, retail FROM books WHERE category = 'COMPUTER') SELECT title, retail FROM ComputerBooks WHERE retail = (SELECT MIN(retail) FROM ComputerBooks) 

The sample I used to validate the syntax.

Answer 2

MIN applies to a group of records, so you need to specify which group of records you have in mind.

If you mean for each name, indicate the minimum retail size, then you need to:

 SELECT MIN(retail), title FROM books WHERE category = 'COMPUTER' GROUP BY title