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.

Answer 1

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;

Answer 2

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) .

Answer 3

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

Answer 4

If you do groping 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.

Answer 5

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.

Answer 6

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);