CODEWITHSUNDEEP
sqloracle-databasegroup-by
Controlla
Controlla

Reputation: 1

Group by error within SELECT statement causing ORA-00979 error code in Oracle

I'm trying to run the following code:

SELECT BOOK.BOOK_NUM AS "Book Number",
       BOOK.BOOK_TITLE AS "Book Title",
       BOOK.BOOK_SUBJECT AS "BOOK SUBJECT",
       ROUND(AVG(BOOK.BOOK_COST),2) as "Subject Avg",
       (BOOK_COST - ROUND(AVG(BOOK.BOOK_COST),2)) AS "COST DIFFERENCE" 
FROM BOOK
GROUP BY BOOK.BOOK_NUM, BOOK.BOOK_TITLE, BOOK.BOOK_SUBJECT

However, when I do, I get an error

ORA-00979: not a GROUP BY expression

The issue does not seem to occur when I remove:

(BOOK_COST - ROUND(AVG(BOOK.BOOK_COST),2)) AS "COST DIFFERENCE"

from the select statement.

I know a GROUP BY statement needs all values from the select statement that aren't functions, and I had thought mine met that requirement.

Any help would be greatly appreciated!

Upvotes: 0

Views: 332

Answers (3)

Dave Costa
Dave Costa

Reputation: 48111

The immediate cause of your error is that you are referencing BOOK_COST as a scalar (not within a grouping function), but it is not part of the GROUP BY expression.

The obvious "solution" would be to add BOOK_COST to the GROUP BY list, but I doubt that will give you the answer you want. In fact, I suspect you already have more columns in the GROUP BY than you really want.

It looks like what you want is to:

  • Compute the average cost of books within each subject
  • List all books and for each, display the difference between that book's cost and the average within its subject

As shown in Gordon's answer, one way to achieve this is by using the windowing version of AVG() with an appropriate partition clause. If my guess about your requirements is correct, then what you want is actually:

SELECT BOOK.BOOK_NUM AS "Book Number",
       BOOK.BOOK_TITLE AS "Book Title",
       BOOK.BOOK_SUBJECT AS "BOOK SUBJECT",
       ROUND(AVG(BOOK.BOOK_COST) OVER (PARTITION BY BOOK.BOOK_SUBJECT), 2) as "Subject Avg",
       (BOOK_COST - ROUND(AVG(BOOK.BOOK_COST) OVER (PARTITION BY BOOK.BOOK_SUBJECT), 2)) AS "COST DIFFERENCE" 
FROM BOOK;

(You could also use a subquery to avoid writing out the window function twice, but that's not really important for this answer.)

Just for illustration (or if you happen to be on an old version of Oracle), here's a way to do it without a window function:

WITH subjects as (
  SELECT book.book_subject, round(avg(book.book_cost),2) as avg_cost
  FROM book
  GROUP BY book.book_subject
)
SELECT
  book.book_num,
  book.book_title,
  book.book_subject,
  subjects.avg_cost,
  book.book_cost - subjects.avg_cost
FROM
  book
JOIN
  subjects ON subject.book_subject = book.book_subject

This does one query against the table to find the average cost within each subject, then joins that with the base table so you can calculate the difference for each individual book.

Upvotes: 1

dmg
dmg

Reputation: 4481

The semantics of group by are:

Any attribute in the select part of the query should either be:

  • In the group by

  • Or as an argument of an aggregate function

In your case,

(BOOK_COST - ROUND(AVG(BOOK.BOOK_COST),2)) AS "COST DIFFERENCE"

BOOK_COST is not in the group by, nor inside an aggregate function.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269553

I think you are looking for window functions, not aggregation functions:

SELECT BOOK.BOOK_NUM AS "Book Number",
       BOOK.BOOK_TITLE AS "Book Title",
       BOOK.BOOK_SUBJECT AS "BOOK SUBJECT",
       ROUND(AVG(BOOK.BOOK_COST) OVER (PARTITION BY BOOK.BOOK_NUM, BOOK.BOOK_TITLE, BOOK.BOOK_SUBJECT), 2) as "Subject Avg",
       (BOOK_COST - ROUND(AVG(BOOK.BOOK_COST) OVER (PARTITION BY BOOK.BOOK_NUM, BOOK.BOOK_TITLE, BOOK.BOOK_SUBJECT), 2)) AS "COST DIFFERENCE" 
FROM BOOK;

This keeps all the original rows in your data and adds aggregated columns onto them.

Upvotes: 0

Related Questions