I have a table 'allorders' which looks like that:

And i would like to get all items from one order as one string the order number which should look like that:

My SQL-Query looks like that:

SELECT Order_Nr, LISTAGG(CONCAT(CONCAT(COUNT(Items), ' x '), Items), ' ') WITHIN GROUP(ORDER by Items) as OrderOverview
FROM allorders 
WHERE Order_Nr = 'O100'
ORDER BY Order_Nr;

I am using Oracle 11g and I get the following error message:

ORA-00937: no groupfunction for single-group 00937. 00000 - "not a single-group group function" *Cause:
*Action:

If i don't select the Order_Nr in the SQL Query it works but i would like to see it in the result for a possible view which i would like to create in the future.

What am I missing? Does my idea not work in general? I hope I have worked up the data okayish, should nevertheless information be missing let me know.

Thanks,

CodePudding user response：

You want to use GROUP BY twice; first, find the COUNT for each item and order pairing and then, second, concatenate the strings with LISTAGG:

SELECT order_nr,
       LISTAGG(counted_items, ' ') WITHIN GROUP (ORDER BY items)
         AS OrderOverview
FROM   (
  SELECT order_nr,
         items,
         COUNT(*) || ' x ' || Items AS counted_items
  FROM   allorders
  WHERE Order_Nr = 'O100'
  GROUP BY
         order_nr,
         items
)
GROUP BY
       order_nr

Which, for the sample data:

CREATE TABLE allorders (Order_Nr, Items) AS
SELECT 'O100', '5X1' FROM DUAL UNION ALL
SELECT 'O100', '5X1' FROM DUAL UNION ALL
SELECT 'O100', '7B3' FROM DUAL UNION ALL
SELECT 'O101', '3A2' FROM DUAL UNION ALL
SELECT 'O101', '3A2' FROM DUAL;

Outputs:

ORDER_NR	ORDEROVERVIEW
O100	2 x 5X1 1 x 7B3

db<>fiddle here

CodePudding user response：

Oracle actually has a unique capability of executing nested aggregate functions

select   max(Order_Nr) as Order_Nr
        ,listagg(count(*) || 'x' || Items, ' ') within group (order by Items) as OrderOverview
from     allorders 
where    Order_Nr = 'O100'
group by Items, Order_Nr

Fiddle