title: Oracle SQL: How to INSERT a SELECT statement with a GROUP BY clause on a table with IDENTITY column?
body: <p>In an application, I intend on truncating and inserting on an Oracle 12c database, but have found this issue with an <code>IDENTITY</code> column. Even though the <code>INSERT... SELECT</code> statement works on most <code>SELECT</code> uses I have tried, when this statement also has a <code>GROUP BY</code> clause, it fails to work, issuing a "ORA-00979: not a <code>GROUP BY</code> expression" complaint. Below is some example code:</p>
  
  <pre><code>create table aux (
    owner_name varchar2(20),
  pet varchar2(20) );
  
  insert into aux values ('Scott', 'dog');
  insert into aux values ('Mike', 'dog');
  insert into aux values ('Mike', 'cat');
  insert into aux values ('John', 'turtle');
  
  create table T1 (
    id number generated always as identity,
    owner_name varchar2(20),
    pet_count number
  );
  insert into T1 (owner_name, pet_count) 
    select owner_name, count(*) as pet_count from aux group by owner_name;
  select owner_name, count(*) as pet_count from aux group by owner_name;
  </code></pre>
  
  <p>It works on the first insert, but fails on the next.</p>
  
  <p>EDIT: I've changed the code so the issue is easier to understand while still reproducible.</p>
  
  <p>Appreciate the help!</p>