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.
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
?
Include in the GROUP BY
clause all SELECT
expressions that are not group function arguments.
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);
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 BY
clause.
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.
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;
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) .
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
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.
The answer of "Aaron Digulla" (the first at this time) inspired my solution for the same error code using Spring Boot 2 (JPA / Hibernate) and CriteriaQuery / CriteriaBuilder.
Make a List of selects, and add it to your criteriaQuery.multiselect()
List<Selection> selects = new ArrayList<>();
selects.add(seccionRoot.get("id"));
selects.add(synSeccionRoot.get("DDF"));
selects.add(synSeccionRoot.get("TTYU"));
selects.add(synSeccionRoot.get("4567"));
selects.add(seccionRoot.get("22").get("223"));
selects.add(tasaRoot.get("price"));
selects.add(tasaRoot.get("chair"));
cq.multiselect(selects.toArray(Selection[]::new));
Then you can cast the List to an Expression[]
cq.groupBy(selects.toArray(Expression[]::new));
Success story sharing