Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ORA-00937: not a single-group group function

Tags:

sql

min

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

works fine, but when I include title in select like:

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

it doesn't. Why? How to make it work?

like image 407
user490735 Avatar asked Apr 07 '11 21:04

user490735


People also ask

How do I fix Ora 00937 Not a single group group function?

To resolve the error, you can either remove the group function or column expression from the SELECT clause or you can add a GROUP BY clause that includes the column expressions. Remember, if you are using an aggregate function in your select query then you must also have a GROUP BY clause.

Which is not a group function in SQL?

Any SELECT expression that is not included in the GROUP function must be listed in the GROUP BY clause. These are AVG, COUNT, MAX, MIN, SUM, STDDEV, and VARIANCE. You may have also tried to execute a SELECT statement that contains a GROUP BY clause.

Is not a GROUP BY expression?

ORA-00979 “ Not a GROUP BY expression ” is an error issued by the Oracle database when the SELECT statement contains a column that is neither listed in GROUP BY nor aggregated. This error message can be confusing to beginners.

What is missing left parenthesis in Oracle?

ORA-00906: missing left parenthesis Cause: A required left parenthesis has been omitted. Certain commands, such as CREATE TABLE, CREATE CLUSTER, and INSERT, require a list of items enclosed in parentheses. Parentheses also are required around subqueries in WHERE clauses and in UPDATE table SET column = (SELECT...)


2 Answers

Rhys's answer is correct, if that is what you mean, but you might have wanted the title(s) where retail=MIN(retail), and that wording suggests how to get that 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 a WITH clause (if you're using a recent 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)

Sample I used to confirm syntax.

like image 158
Mark Hurd Avatar answered Nov 06 '22 18:11

Mark Hurd


MIN applies to a group of records, so you need to tell it which group of records you mean.

If you mean for each title, show the minimum of retail, then you need:

SELECT MIN(retail), title FROM books
WHERE category = 'COMPUTER'
GROUP BY title
like image 41
Rhys Gibson Avatar answered Nov 06 '22 18:11

Rhys Gibson