Oracle SQL - Concat + Listagg + Count does not work together

user_6720566 0
Viewed 32

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

Order_Nr Items
O100 5X1
O100 5X1
O100 7B3
O101 3A2
O101 3A2

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

Order_Nr OrderOverview
O100 2 x 5X1 1 x 7B3

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,

listagg oracle11g sql-order-by oracle sql
2 Answers

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

user_15092640

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
ORDER_NR ORDEROVERVIEW
O100 2x5X1 1x7B3

Fiddle

user_63364790
Related