René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

group by

select
  col_1,
  col_2,
  ...
  aggregate_function_1(col_3),
  aggregate_function_2(col_4),
  aggregate_function_3(col_5),
  ...
from
  table 1 ...
group by
  col_1,
  col_2

Errors related to group by

There are two ORA-errors closely related to group by.
In order to demonstrate these, the following table is created... 
create table ora_00979_00937_test (
  a number,
  b number,
  c varchar2(20)
);
... and filled with some values: 
insert into ora_00979_00937_test values (1, 1, 'New York');
insert into ora_00979_00937_test values (1, 1, 'Geneva');
insert into ora_00979_00937_test values (1, 2, 'Berlin');
insert into ora_00979_00937_test values (1, 2, 'Sidney');
insert into ora_00979_00937_test values (2, 4, 'Rio de Janeiro');
insert into ora_00979_00937_test values (2, 4, 'Tokyo');
insert into ora_00979_00937_test values (2, 5, 'Oslo');
insert into ora_00979_00937_test values (2, 6, 'Moscow');

ORA-00979: not a GROUP BY expression

An ORA-00979 is issued if not all non-aggregate columns (here: a and b) are explicitely listed as well in the group by clause as shown in the following select statement: 
select 
  a, b,
  max(c)
from
  ora_00979_00937_test
group by 
  a;
However, the following statement runs without error. It lists all non-aggregate columns in the group by part. Additional columns listed in the group by clause don't prevent the statement from running: 
select 
  a,
  max(c)
from
  ora_00979_00937_test
group by 
  a, b;
Here's the output: 
         A MAX(C)
---------- -------------------
         2 Tokyo
         2 Oslo
         1 Sidney
         1 New York
         2 Moscow

ORA-00937: not a single-group group function

An ORA-00937 is issued if there is at least an aggregated column and a non-aggregated column without a group by clause as shown in the following select statement: 
select 
  a, b,
  max(c)
from
  ora_00979_00937_test;
See also ORA-00935.