CODEWITHSUNDEEP
oracle-database
Xavier
Xavier

Reputation: 11

Oracle SQL: error not a single-group group function

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

Upvotes: 1

Views: 195

Answers (1)

MT0
MT0

Reputation: 167972

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.

Upvotes: 1

Related Questions