Getting ORA-00979 not a group by expression /ERROR

Question

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
;

Never, ever store data as separated items. It will only cause you lots of problems. — jarlh, Sep 28 '22 at 14:57
you have semicolon in your table posts but you check for comma in your statement, besides for that query to work you need also no spaces in your data, so you need to remove them final please read https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — nbk, Sep 28 '22 at 15:06
The error is because you only group by `TAG.NAME`, but you have three other non-aggregated columns in the select list - given your desired output you probably just need to remove those. (And fix the comma/semicolon discrepancy, as nbk said; but that won't error, just give wrong results). Is your outer join the right way round though? — Alex Poole, Sep 28 '22 at 15:07
@nbk - yes, but the bad data model and comma/semicolon/space issues aren't causing the ORA-00979 *8-) — Alex Poole, Sep 28 '22 at 15:11
Thank you for your support, team, @Isolated the data type is VARCHAR2 — CodingStudent, Sep 28 '22 at 15:58
I can later correct the issue of the space and the semicolon when entering the data, but for the moment I have a table full of data, any solution is welcome — CodingStudent, Sep 28 '22 at 16:09

score 0 · Answer 1 · answered Sep 28 '22 at 16:46

0

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

answered Sep 28 '22 at 16:46

DannySlor

4,574
1
3
11

Thank you for replying, i tried this but i dont get any result except columns name NAME and NUB_OF_POST i don't know what i'm missing – CodingStudent Sep 28 '22 at 17:52
@CodingStudent I suggest going to the fiddle I posted and looking at each level separately. Check if we defined everything the same and if the names are exactly the same. – DannySlor Sep 28 '22 at 18:04
i had replaced '\d' with '[^;]+' i start get result but still an issue it's take only the first tag for each post – CodingStudent Sep 28 '22 at 19:10
Send me your fiddle and I'll take a look – DannySlor Sep 28 '22 at 20:39

score 0 · Answer 2 · answered Sep 28 '22 at 20:20

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>

Getting ORA-00979 not a group by expression /ERROR

2 Answers2