By Theresa


2009-10-05 14:57:21 8 Comments

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.

8 comments

@Mike Palmice 2019-02-11 14:57:25

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

@Muhammad Nadeem 2016-04-07 07:26:28

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.

@fg78nc 2017-07-11 06:49:06

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.

@Vijay Gupta 2017-03-23 10:52:10

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

@Pavel Zimogorov 2016-04-19 06:28:16

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;

@Xaisoft 2009-10-05 15:01:47

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

@Aaron Digulla 2009-10-05 15:02:02

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?

@Theresa 2009-10-05 15:09:02

even those that are in the ORDER BY clause? I don't have those two in my GROUP BY.

@Xaisoft 2009-10-05 15:10:43

No, you do not need to put them in your order by clause

@Theresa 2009-10-05 15:12:36

I tried adding the two columns in the ORDER BY to the GROUP BY. That worked. Thanks!

@Aaron Digulla 2009-10-08 08:23:25

Or to put it another way: If you have two columns and group by the first, that means you'll have several values from the second column per result row. Since there is only a single result row but many values to choose from, which one should the DB return? The first it stumbles upon?

@Chris Baker 2014-08-27 21:32:52

@AaronDigulla That's what MySQL does, and the world didn't end :p

@Aaron Digulla 2014-08-28 09:01:42

@Chris: Yeah, well, I prefer software which doesn't hide stupid mistakes from me which take endless hours to find :-)

@ClaireG 2015-05-25 18:47:42

Thank you! You just solved a problem I've been trying to solve for two days!

@g.a 2017-03-06 17:17:28

I partially agree. But assume the following case: there are 4 columns: A,B, C and D. Now I set (A,B,C) as a composite key. Then "select A,B,max(C), D ... group by A, B" is not ambiguous, since for each combination of A,B and C, D is already defined. Still oracle refuses to do its job.

@toonice 2017-04-19 00:02:25

The A, B grouping would have several potential values for C and D. The MAX() function returns the group's maximum for that field only - it does not return the whole row, nor could it. Firstly, even though the maximum value of C will be unique within its group in this scenario, in others it may not be. e.g. A count of Employees for each Store might have two records with the maximum value. Secondly, if you had also had MAX( E ) then the two MAX() values could easily come from different records within the group.

@Joseph Lust 2011-04-26 18:26:35

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

@Joseph Lust 2015-11-21 00:14:43

@GlennFromIowa given my pseudo table isn't rigorously defined above, and that I not longer work for a firm with 11g, I can't provide a better example, though it was a problem when I last tested it.

@Kyle Bridenstine 2017-10-17 17:48:06

Just out of curiosity what is an example of when you would want a random result? I can't think of any reason you would want to group by FOO and get a random row for BAR.

Related Questions

Sponsored Content

14 Answered Questions

[SOLVED] Select first row in each GROUP BY group?

2 Answered Questions

ORA-00979 ORA-22818

  • 2016-04-29 23:12:22
  • SKINNY
  • 228 View
  • -1 Score
  • 2 Answer
  • Tags:   oracle ora-00979

2 Answered Questions

[SOLVED] ORA-00979 Not a group by expression?

  • 2015-01-10 16:34:58
  • user4429915
  • 1294 View
  • 0 Score
  • 2 Answer
  • Tags:   sql oracle group-by

1 Answered Questions

[SOLVED] ORA-00979: not a GROUP BY expression (Case statement issue)

2 Answered Questions

[SOLVED] Group by with 3 joins

  • 2017-01-20 21:23:50
  • Jose Rojas
  • 52 View
  • 0 Score
  • 2 Answer
  • Tags:   sql oracle

3 Answered Questions

0 Answered Questions

How do I group Oracle query results by an expression?

  • 2016-03-28 22:30:26
  • Barry Fruitman
  • 76 View
  • 0 Score
  • 0 Answer
  • Tags:   sql oracle group-by

1 Answered Questions

0 Answered Questions

"ORA-00979: Not a Group By expression" with Hibernate HQL

2 Answered Questions

[SOLVED] ORA-00979: not a GROUP BY expression issue

Sponsored Content