Ошибка ora 00979

asked Oct 5, 2009 at 14:57

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

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.

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;

answered Apr 19, 2016 at 6:28

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

answered Mar 23, 2017 at 10:52

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

answered Feb 11, 2019 at 14:57

Adding an alternative solution for @Joseph Lust’s answer regarding random rows in each group. This is made possible in Oracle Database 19c or newer by using any_value keyword. Here is an example:

select customer_id,
       max ( order_datetime ),
       any_value ( store_id ),
       any_value ( order_status ),
       any_value ( order_id )
from   co.orders
group  by customer_id;

answered Oct 18, 2022 at 7:49

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

answered Apr 27, 2022 at 14:46

Why does this work

SELECT DISTINCT FIRSTNAME, LASTNAME
FROM books, CUSTOMERS, orders, orderitems
WHERE STATE IN('FL ', 'GA')
GROUP BY orders.order#, firstname, lastname
HAVING SUM(retail*quantity) > 80  

but when firstname, lastname is removed from group by it doesn’t?

ORA-00979: not a GROUP BY expression