Is there any logic to use group by with aggregate and cases?

Refer below for source data :

|Id  |  Value 
| C1 |  0.76
| C1 |  <0.65
| C1 |  <0.35
| C2 |  0.27
| C2 |  3.7 
| C3 |  <2.5
| C3 |  <3.6

I have to transform this , with a condition that :

If all value for certain id is starting with this "<" , append into a single string
if all the values are numeric, average them.
if any value for certain id is starting with this "<" append it

Expected Output :

|Id  |  Value 
| C1 |  0.76,<0.65,<0.35
| C2 |  1.985
| C3 |  <2.5,<3.6

I have tried aggregating it with query, but not able to average it using case

Used query :

Select id,
case when value like '%<%' then listagg(value,',')
     else avg(cast(value as decimal(38,10))) 
 end as Value
from table

Issue is two things,

not able to average the numerical values
how to handle one numerical and one string value here to append

Solution

A couple of points...

You're trying to use functions that don't exist in PostgreSQL. So, I've swapped them to PostgreSQL functions.

You need to safely convert strings to numbers (else the '<' will cause errors).

You can't check a scalar value (from before GROUP BY / aggregation) in a CASE expression, then return an aggregate. You need to check a scalar and return a scalar or check an aggregate and return an aggregate. (The following does the latter.)

A column can't contain strings and numeric values, so the numerics must be converted back to strings.

WITH
  safe_agg AS
(
  SELECT
    id,
    STRING_AGG(value, ',')           AS string_list,
    AVG(TO_NUMBER(value, '999.999')) AS number_avg
  FROM
    table
  GROUP BY
    id
)
SELECT
  id,
  CASE
    WHEN string_list LIKE '%<%'
    THEN string_list
    ELSE number_avg::TEXT
  END
FROM
  safe_agg

Demo : https://dbfiddle.uk/y5DakXm6