CODEWITHSUNDEEP
sqloracle-database
Silverrook
Silverrook

Reputation: 51

ORA-00937: "not a single-group group function"

I have select, where I want to collect one line only: the listagg (like in code below) which has to be restricted on

  1. 40 lines max (because of limited varchar length) and
  2. total count of all lines with "P1 = 'Y'" (P1 flag can be only Y or null).


I did it separately in two selects, but I want it to be nice and smooth.
This code unfortunately gives me ORA-00937: "not a single-group group function". Any suggestions how to do it right?

ls_list := '';
select LISTAGG(A || '.' || B||'('||to_char(C)||')',',')
         WITHIN GROUP (ORDER BY B) as Segments
         ,count(*) over (partition by P1)    --this count is the problem
   into ls_list, ll_total
   from xmltable('DBStatus/Body/Segments/Segment' passing gx_status
                      columns A VARCHAR2(30) path '@A'
                             ,B VARCHAR2(30) path '@B'
                             ,C NUMBER path '@C'
                             ,P1 CHAR(1) path '@P1'
                )
   where P1 = 'Y'
         and
         rownum <= 40;

Upvotes: 3

Views: 781

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

You are mistakenly using the aggregate LISTAGG rather than the analytic function. Add an OVER clause to LISTAGG and you are fine.

select 
  listagg(a || '.' || b||'('||to_char(c)||')',',')
    within group (order by b) 
    over (partition by p1) as segments,
  count(*) 
    over (partition by p1)
...

(Or remove the OVER clause from COUNT, so as to use its aggregate version, too.)

Upvotes: 4

Related Questions