Listagg Query "Ora-00937: Not a Single-Group Group Function"

LISTAGG Query ORA-00937: not a single-group group function

I think for your query to work, you need to add a group by, change the order by. You should also use proper explicit join syntax:

SELECT r.rule_id as RULE_ID, 
       LISTAGG(a.ATTRIBUTE_ID, ', ') WITHIN GROUP (ORDER BY a.ATTRIBUTE_ID) as "ATTR_IDS"
FROM N_RULE r JOIN
     N_ATTRIBUTE a 
     ON r.RULE_ID = a.RULE_ID 
GROUP BY r.rule_id
ORDER BY r.rule_id;

Or, possibly you want to include other attribute in the results:

SELECT r.rule_id, r.AUDIENCE, UPPER(r.NAME) 
       LISTAGG(a.ATTRIBUTE_ID, ', ') WITHIN GROUP (ORDER BY a.ATTRIBUTE_ID) as "ATTR_IDS"
FROM N_RULE r JOIN
     N_ATTRIBUTE a 
     ON r.RULE_ID = a.RULE_ID 
GROUP BY r.rule_id, r.AUDIENCE, UPPER(r.NAME)
ORDER BY r.AUDIENCE, UPPER(r.NAME);

not a single-group group function' in oracle when using LISTAGG

you have a missing group by, you don't need distinct() if you are grouping by UNID

SELECT AMD.UNID AS APPLICATION_REF_ID, 
LISTAGG(LOC.PLT,',') WITHIN GROUP (ORDER BY LOC.PLT ) AS MYLOC  
FROM TAB1 AMD
LEFT JOIN TAB2 PER
ON NVL (AMD.PERMITNEWID, AMD.PERMITID) = PER.UNID
LEFT JOIN TAB3 LOC
ON NVL(AMD.PERMITNEWID, AMD.PERMITID) = LOC.PERMITID
WHERE NVL (AMD.PERMITNEWID, AMD.PERMITID) = PER.UNID
GROUP BY AMD.UNID

ORA-00937: not a single-group group function when using rownum

Extending from Gordon's solution, a workaround for using rownum

WITH street_list as
(
 SELECT streetid,
    roadid,
    LISTAGG(longitude ,',') WITHIN GROUP ( ORDER BY sequence_number) long_agg ,
    LISTAGG(latitude ,',')  WITHIN GROUP ( ORDER BY sequence_number) lat_agg ,
  FROM linkstable
  WHERE areaid = 100
 group by strretid,roadid 
),
street_list_numbered as
(
  SELECT rownum r,streetid,roadid, long_agg,lat_agg 
 FROM street_list
)
SELECT * FROM street_list_numbered
WHERE r > 100  and r <  200;

Join in SQL query gives ORA-00937 error

ORA-00937: Simply means you require GROUP BY clause, and in that clause repeat each column of the select clause that does NOT use an aggregate function such as SUM/MIN/MAX etc.

    SELECT
       departments.department_name
     , LISTAGG (employees.first_name || employees.last_name, '; ' )
           WITHIN GROUP (ORDER BY employees.last_name,employees.last_name) employee_names
     , SUM(employees.salary)
    FROM employees
    INNER JOIN departments ON employees.department_id = departments.department_id
    WHERE departments.department_id = 100
    GROUP BY
       departments.department_name
    ; 

Note I am using LISTAGG() but you don't have to use that. If your 3 columns need to be Department, Department Manager and (sum of salary), then you need to choose the correct field from the department table for Manager and then include that column in the select and group by clauses.

ListAgg and other column selection

Don't use sub-query.

select listagg((REGEXP_SUBSTR(ABC, '[^:]+$')),';') AS Unit , 
       'XYZ' 
  from MNO 
 where BATCH in (select BATCH from PQR where TYPE = 'Emp' and ORG = 'XYZ')

not a single-group group function with case expression

Use CASE inside LISTAGG:

WITH sample AS (SELECT 1 AS EXTRACT_ORDRE, 'tab1' AS EXTRACT_TABLE, 'col1' AS EXTRACT_COLONNE, 'name1' AS EXTRACT_LIBELLE FROM DUAL
                    UNION ALL
                    SELECT 2, 'tab2', 'col2', 'name2' FROM DUAL
                    UNION ALL
                    SELECT 3, NULL, 'col3', 'name3' FROM DUAL
                    )
    select 'SELECT '||LISTAGG(CASE WHEN EXTRACT_TABLE IS NOT NULL THEN (EXTRACT_TABLE||'.'||EXTRACT_COLONNE ||' AS " '|| EXTRACT_LIBELLE || ' " ') 
                ELSE EXTRACT_COLONNE ||' AS " '|| EXTRACT_LIBELLE || ' " ' END ,',') WITHIN GROUP(ORDER BY EXTRACT_ORDRE) AS result
    from sample;

Output:

RESULT
-------------------
SELECT tab1.col1 AS " name1 " ,tab2.col2 AS " name2 " ,col3 AS " name3 "

How to use LISTAGG to concatenate from multiple rows?

When you use an aggregate function (that collapses multiple rows into one) you need a GROUP BY clause, so you'd need something like this:

SELECT i.username, 
       LISTAGG( c.course, ', ' ) WITHIN GROUP ORDER BY ( c.course )
  FROM app_users i
   ...
  LEFT JOIN course c on sl.course = c.id
 GROUP BY i.username

Basically, anything that's not being aggregated, needs to be in the GROUP BY clause. Try it in a much simpler query until you get the hang of it, then make your big one.

Using select in the ELSE of a CASE statement gives me ORA-00937: not a single-group group function

Taking a slightly different approach but it appears to work.
Instead of casing and doing a count, simply check if the aggregate is null (coalesce returns the first non-null value in a series) and if it is substitute your message. This avoids a 2nd level grouping which I don't believe is needed.

Too bad listagg doesn't support distinct within the aggregate as well; we could avoid the inline view.

SELECT coalesce(listagg(A.osuser, ', ') within group (order by A.osuser), 
                'There are no users connected') as userList
FROM (select distinct osuser from v$session) A
WHERE A.osuser!= 'SYSTEM' and A.osuser not like 'VMCONFTEST%'

This does have the overhead in that it attempts to generate a list of users which your case statement may be attempting to short circuit. However if there are no records in V$session the select distinct should be quick.

Though to be honest I'm not sure why we need to do this. Null in the list is generally an adequate response indicating no users. and the UI would handle null meaning no users.

May even be faster if we more the where clause to the inline view..

SELECT coalesce(listagg(A.osuser, ', ') within group (order by A.osuser), 
                'There are no users connected') as userList
FROM (SELECT distinct osuser 
      FROM v$session
      WHERE A.osuser!= 'SYSTEM' 
        and A.osuser not like 'VMCONFTEST%') A

---

Related Topics