ORA-00979 not a group by expression

user_39355 0
Viewed 534641

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.

ora-00979 oracle sql
9 Answers

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_60273790

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