Selecting min(value) and max(value) from a column and returning it in a single row Using Listagg in Oracle

Question

I have a table like below

gen_id pos_id Language Value
1221    1234    EN      1.0 m
1221    1234    EN      2.0 m
1221    1234    EN      3.0 m
1221    1234    EN      4.9 m

I wanted to select the value as : "1.0 m - 4.9 m".

Select the min(value) and max(value) separated by a string "-".

I tried the below listagg but it throws the error "ORA-00937: not a single-group group function 00937. 00000 - "not a single-group group function"".

Select  LISTAGG (min(value) || '-' || max(value))   
             WITHIN GROUP (ORDER BY pos_id)
             OVER (PARTITION BY geng_id,pos_id,language)
From TSS_Gen where geng_id = 1221 and Pos_id = 1234;

Answer 1

Firstly, do NOT store numbers as string with units; store them as numbers.

---

Assuming numeric values then you do not need to use LISTAGG (or an analytic function) and can simply aggregate and use string concatenation:

SELECT min(value) || ' m - ' || max(value) || ' m'
FROM   TSS_Gen
WHERE  geng_id = 1221
AND    Pos_id = 1234;

If you are storing units then assuming that they are all metres then:

SELECT min(TO_NUMBER(SUBSTR(value, 1, LENGTH(value) - 2))) || ' m'
       || ' - '
       || max(TO_NUMBER(SUBSTR(value, 1, LENGTH(value) - 2))) || ' m'
FROM   TSS_Gen
WHERE  geng_id = 1221
AND    Pos_id = 1234;