ORA-00979 not a group by expression

Question

I am getting ORA-00979 with the following query   SELECT cr review sk  cr cs sk  cr full name  tolist to char cf fact date   mm dd yyyy     appt   cs cs id  cr tracking number from review cr  cs  fact cf where cr cs sk   cs cs sk and UPPER cs cs id  like        UPPER i cs id         and row delete date time is null and cr review sk   cf review wk     and cr fact type code       183050 GROUP BY cr review sk  cr cs sk  cf fact date  cr tracking number ORDER BY cs cs id  cr full name    I couldn t find any examples that had both GROUP BY and ORDER BY clauses in the same query   I tried removing each field from the group by one at a time  but am still getting the same error

User · Answer

If you do grouping by virtue of including GROUP BY clause, any expression in SELECT, which is not group function (or aggregate function or aggregated column) such as COUNT, AVG, MIN, MAX, SUM and so on (List of Aggregate functions) should be present in GROUP BY clause.

Example (correct way) (here employee_id is not group function (non-aggregated column), so it must appear in GROUP BY. By contrast, sum(salary) is a group function (aggregated column), so it is not required to appear in the GROUP BYclause.

   SELECT employee_id, sum(salary) 
   FROM employees
   GROUP BY employee_id;

Example (wrong way) (here employee_id is not group function and it does not appear in GROUP BY clause, which will lead to the ORA-00979 Error .

   SELECT employee_id, sum(salary) 
   FROM employees;

To correct you need to do one of the following :

Include all non-aggregated expressions listed in SELECT clause in the GROUP BY clause
Remove group (aggregate) function from SELECT clause.

User · Answer

You must put all columns of the SELECT in the GROUP BY or use functions on them which compress the results to a single value  like MIN  MAX or SUM    A simple example to understand why this happens  Imagine you have a database like this   FOO BAR 0   A 0   B   and you run SELECT   FROM table GROUP BY foo  This means the database must return a single row as result with the first column 0 to fulfill the GROUP BY but there are now two values of bar to chose from  Which result would you expect - A or B  Or should the database return more than one row  violating the contract of GROUP BY

User · Answer

The group by is used to aggregate some data  depending on the aggregate function  and other than that you need to put column or columns to which you need the grouping   for example    select d deptno  max e sal   from emp e  dept d where e deptno   d deptno group by d deptno    This will result in the departments maximum salary   Now if we omit the d deptno from group by clause it will give the same error

User · Answer

Same error also come when UPPER or LOWER  keyword not used in both place in select expression and group by expression    Wrong  -  select a   count    from my table group by UPPER a      Right  -  select UPPER a    count    from my table group by UPPER a

User · Answer

Too bad Oracle has limitations like these  Sure  the result for a column not in the GROUP BY would be random  but sometimes you want that  Silly Oracle  you can do this in MySQL MSSQL   BUT there is a work around for Oracle   While the following line does not work  SELECT unique id col  COUNT 1  AS cnt FROM yourTable GROUP BY col A    You can trick Oracle with some 0 s like the following  to keep your column in scope  but not group by it  assuming these are numbers  otherwise use CONCAT   SELECT MAX unique id col  AS unique id col  COUNT 1  AS cnt  FROM yourTable GROUP BY col A   unique id col 0   col A

User · Answer

You should do the following    SELECT cr review sk          cr cs sk          cr full name         tolist to char cf fact date   mm dd yyyy     appt          cs cs id          cr tracking number from review cr  cs  fact cf where cr cs sk   cs cs sk        and UPPER cs cs id  like        UPPER i cs id                and row delete date time is null        and cr review sk   cf review wk            and cr fact type code       183050 GROUP BY cr review sk  cr cs sk  cf fact date  cr tracking number  cs cs id  cr full name ORDER BY cs cs id  cr full name

User · Answer

In addition to the other answers  this error can result if there s an inconsistency in an order by clause  For instance   select      substr year month  1  4       count    as tot from     schema tbl group by     substr year month  1  4  order by     year month

User · Answer

Include in the GROUP BY clause all SELECT expressions that are not group function arguments

[sql] ORA-00979 not a group by expression

Examples related to sql

Examples related to oracle

Examples related to ora-00979