SQLerror, getting error in group by function-CodePudding

AUD_ID TITLE_ID DATES
------ -------- --------------------
   102        2 2021
   101        1 2021
   103        3 2022

select * from titles;

TITLE_ID TITLE                TYPE                 PRICE
-------- -------------------- -------------------- --------------------
       1 s1
       2 s2
       3 s2

select 
    titles.title,
    count(titleauth.aud_id) as ors,
    titleauth.dates 
from 
    titleauth 
join 
    titles on titleauth.title_id = titles.title_id 
group by 
    titleauth.dates;

ERROR at line 1:
ORA-00979: not a GROUP BY expression

select 
    count(titleauth.aud_id) as ors,
    titleauth.dates 
from 
    titleauth 
join 
    titles on titleauth.title_id = titles.title_id 
group by 
    titleauth.dates;

       ORS DATES
---------- --------------------
         2 2021
         1 2022

in first query where I included titles.title in select part getting error, but when I removed title select, it's working well. want to know the reason, please help.

CodePudding user response：

the column without aggregation function must be declared oin group by clause

    select 
        titles.title,
        count(titleauth.aud_id) as ors,
        titleauth.dates 
    from 
        titleauth 
    join 
        titles on titleauth.title_id = titles.title_id 
    group by 
        titleauth.dates, titles.title;

or you need a (fake) agggregation function on titles.title eg:

    select 
        min(titles.title),
        count(titleauth.aud_id) as ors,
        titleauth.dates 
    from 
        titleauth 
    join 
        titles on titleauth.title_id = titles.title_id 
    group by 
        titleauth.dates;

CodePudding user response：

Apart from what ScaisEdge has already told you, appropriate aggregate function might be listagg as it lets you "list" all titles (note, though, that it has limit - the result can't be longer than 4000 characters, but - in this example, you're far from that).

Sample data in lines #1 - 11; query begins at line #12.

SQL> with
  2  titleauth (aud_id, title_id, dates) as
  3    (select 102, 2, 2021 from dual union all
  4     select 101, 1, 2021 from dual union all
  5     select 103, 3, 2022 from dual
  6    ),
  7  titles (title_id, title, type, price) as
  8    (select 1, 's1', null, null from dual union all
  9     select 2, 's2', null, null from dual union all
 10     select 3, 's3', null, null from dual
 11    )
 12  select
 13    listagg(t.title, ', ') within group (order by t.title) as list_of_titles,
 14    count(a.aud_id) as ors,
 15    a.dates
 16  from titleauth a join titles t on t.title_id = a.title_id
 17  group by a.dates;

LIST_OF_TITLES              ORS      DATES
-------------------- ---------- ----------
s1, s2                        2       2021
s3                            1       2022

SQL>