This is a follow up to post: How to INSERT a SELECT statement with a GROUP BY clause on a table with IDENTITY column?
I just ran into this issue with SQL Error: ORA-00979: not a GROUP BY expression.
Tested under 11g, with no Identity column and it works fine. The solution under the other post, suggesting to use /*+ materialize */, didn't work for me under 12c either.
Any other working solutions?
Below are couple test scripts based on the script in the original post.
12c - generates error
===============
create table details1 ( owner_name varchar2(20), pet varchar2(20) );
insert into details1 values ('Scott', 'dog');
insert into details1 values ('Mike', 'dog');
insert into details1 values ('Mike', 'cat');
insert into details1 values ('John', 'turtle');
create table summary1 (
id number generated always as identity, -- 12c Identity column
owner_name varchar2(20),
pet_count number );
select owner_name, count(*) as pet_count from details1 group by owner_name; -- works just fine
insert into summary1 (owner_name, pet_count) select owner_name, count(*) as pet_count from details1 group by owner_name;
11g: - works fine
==========================
create table details ( owner_name varchar2(20), pet varchar2(20) );
insert into details values ('Scott', 'dog');
insert into details values ('Mike', 'dog');
insert into details values ('Mike', 'cat');
insert into details values ('John', 'turtle');
create table summary (
id number , -- note: not an Identity column
owner_name varchar2(20),
pet_count number );
select owner_name, count(*) as pet_count from details group by owner_name;
insert into summary (owner_name, pet_count) select owner_name, count(*) as pet_count from details group by owner_name;