Getting ORA-00979 not a group by expression /ERROR-CodePudding

I'm working on a query but I'm facing a problem I am getting ORA-00979 with the following query:

Thank you in advance

SELECT POST.ID, POST.TAG_ID, TAG.ID, TAG.NAME, COUNT(*) AS TOTAL
    FROM POST
        LEFT JOIN TAG ON ',' || TAG.ID || ',' LIKE '%,' || POST.TAG_ID || ',%'
        GROUP BY TAG.NAME
;

CodePudding user response：

We're using regexp_substr to split up TAG_ID, group by the result and then join TAG TABLE.

with t as  (
            select     regexp_substr(TAG_ID,'\d',1,level) as ID
                      ,count(*)                           as NUB_of_POSTS
            from       post  
            connect by ID = prior ID and level <= regexp_count(TAG_ID,'\d') and sys_guid() <> prior sys_guid()
            group by   regexp_substr(TAG_ID,'\d',1,level)
           )
select     NAME
          ,NUB_of_POSTS
from       t join tag using(ID)

NAME	NUB_OF_POSTS
TAG_1	3
TAG_2	3
TAG_3	4

Fiddle

CodePudding user response：

Similarly ...

Sample data:

SQL> with
  2  post (id, tag_id) as
  3    (select 1, '1; 2; 3' from dual union all
  4     select 2, '1; 2'    from dual union all
  5     select 3, '3'       from dual union all
  6     select 4, '1; 3'    from dual union all
  7     select 5, '2; 3'    from dual
  8    ),
  9  tag (id, name) as
 10    (select 1, 'TAG_1' from dual union all
 11     select 2, 'TAG_2' from dual union all
 12     select 3, 'TAG_3' from dual
 13    ),

Query begins here; see comments within code

 14  -- split POSTs TAG_ID into rows
 15  temp as
 16    (select p.id,
 17            trim(regexp_substr(p.tag_id, '[^;] ', 1, column_value)) tag_id
 18     from post p cross join
 19       table(cast(multiset(select level from dual
 20                           connect by level <= regexp_count(p.tag_id, ';')   1
 21                          ) as sys.odcinumberlist ))
 22    )
 23  -- finally, a simple join between TAG and TEMP with the COUNT aggregate function
 24  select t.name as tag,
 25         count(*) num_of_posts
 26  from tag t join temp p on p.tag_id = t.id
 27  group by t.name;

TAG   NUM_OF_POSTS
----- ------------
TAG_1            3
TAG_2            3
TAG_3            4

SQL>