CODEWITHSUNDEEP
sqloracle-databasegroup-byaggregationstring-aggregation
keewee279
keewee279

Reputation: 1654

Oracle SQL: error not a single-group group function in Select with Xmlagg

I have a table setup that returns multiple rows of column i.AIN for each column p.S_ID so I am trying to aggregate these to get just one row for each unique i.AIN record.

I cannot use Listagg as I am exceeding the character limit there.

With information from this website I created the below query but I get the following error: 

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

Can someone tell me how I have to change my GROUP BY here to make this work or if there is anything else wrong with my query ?

My query (shortened): 

ALTER SESSION ENABLE PARALLEL QUERY;
SELECT
    p.S_ID AS ID
    , XMLELEMENT
    (
        "AINs", XMLAGG
        (
            XMLELEMENT
            (
                "AIN", i.AIN || 
                '-nl-Article: ' || SUBSTR(a.AIN_NAME, 1, 50) || '...' || 
                '-nl-Quantity: ' || SUM(i.UNITS) || 
                '-nl-Price: ' || TO_CHAR(i.PRICE, 'FM9,990.00') || ' + ' || TO_CHAR(i.PRICE_TAX, 'FM9,990.00') || ' USt = ' || TO_CHAR((i.PRICE + i.PRICE_TAX), 'FM9,990.00') || 
                '-nl------' || 
                '-nl-Subtotal: ' || TO_CHAR((i.PRICE * SUM(i.UNITS)), 'FM9,990.00') || ' + ' || TO_CHAR((i.PRICE_TAX * SUM(i.UNITS)), 'FM9,990.00') || ' USt = ' || TO_CHAR(((i.PRICE + i.PRICE_TAX) * SUM(i.UNITS)), 'FM9,990.00')
            )
        )
    )/*.EXTRACT('//text()')*/ AS Details
FROM 
    ITEMS i
LEFT JOIN 
    AINS a
    ON i.AIN = a.AIN
LEFT JOIN 
    PKGS p
    ON i.SHIPMENT = p.SHIPMENT
WHERE
    /*...*/
GROUP BY
    p.S_ID
    , i.AIN
    , a.AIN_NAME
    , i.UNITS
    , i.PRICE
    , i.PRICE_TAX
ORDER BY
    p.S_ID

Many thanks for any help with this - it is much appreciated,
Mike

Upvotes: 0

Views: 914

Answers (1)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

Without real data and tables, it is difficult to provide working an example. You have to split your aggregation in two level. First, create inline view and calculate subtotals, and in next level aggregate results into one string.

  select xmlagg(....
            ) 
            from (
            select i.AIN as ain,  SUBSTR(a.AIN_NAME, 1, 50) as ain_name
                , SUM(i.UNITS) sum_units 
                , TO_CHAR(max(i.PRICE), 'FM9,990.00') || ' + ' || TO_CHAR(max(i.PRICE_TAX), 'FM9,990.00') || ' USt = ' || TO_CHAR((max(i.PRICE) + max(i.PRICE_TAX)), 'FM9,990.00') 
               ... etc
               from /*joins*/
               GROUP BY
              p.S_ID
            , i.AIN
            , a.AIN_NAME 

            )

Upvotes: 1

Related Questions