Oracle SQL: error not a single-group group function

Question

I'm getting error 'not a single-group group function. Following the documentation i have a group by with the column that is not agreggated in the select statement, but is not working.

SELECT
  XMLELEMENT(
    "CLASIFICATION",
    XMLATTRIBUTES(vm.value As "name"),
    XMLAgg(
      XMLElement(
        "GRUP",
        XMLATTRIBUTES(c.description_group As "name"),
        XMLAgg(
          XMLElement(
            "CLAUSE",
            XMLATTRIBUTES(cla.description_clause As "name")
          )
        )
      )
    )
  )
FROM
  dbo.clause cla,
  dbo.clause_group c,
  dbo.values vm
where
  vm.table_name = 'CLAUSE_TYPES'
  and c.code = vm.code
  and cla.id_group = c.id_group
GROUP BY
vm.value

MT0 · Accepted Answer · 2021-12-03 10:32:53Z

Rather than trying to use two aggregation functions with one nested inside the other, use a sub-query:

SELECT XMLELEMENT(
         "CLASIFICATION",
         XMLATTRIBUTES(value As "name"),
         XMLAgg(
           XMLElement(
             "GRUP",
             XMLATTRIBUTES(description_group As "name"),
             clauses
           )
         )
       )
FROM   (
  SELECT vm.value,
         c.description_group,
         XMLAgg(
           XMLElement(
             "CLAUSE",
             XMLATTRIBUTES(cla.description_clause As "name")
           )
         ) AS clauses
  FROM   dbo.clause cla
         INNER JOIN dbo.clause_group c
         ON cla.id_group = c.id_group
         INNER JOIN dbo.values vm
         ON c.code = vm.code
  WHERE  vm.table_name = 'CLAUSE_TYPES'
  GROUP BY vm.value,
         c.description_group
)
GROUP BY value

Note: this is untested as you have not provided the DDL/DML statements for your tables and sample data.

Collectives™ on Stack Overflow

Oracle SQL: error not a single-group group function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related