SQL: Conditional mean based on groups

Suppose I have a data like this

GROUP ID     VALUE 
---------- ---------- 
        1       4 
        1       7 
        1       2 
        1       3 
        1       5 
        2       8 
        2       6
        2       3 
        2       6
        2       1 
        3       7 
        3       6 
        3       2 
        3       1 

I want to calculate the average of VALUE by Group ID, but conditional on VALUE being smaller or equal than the median by group.

So for instance, for Group 1, the median is 4 and 2,3,4 are smaller or equal than 4, so it should give for group 1:

GROUP ID     COND AVG 
---------- ---------- 
        1       4 
...

Does anyone have a clue how I can do this in Oracle/SQL?

Answer

You can calculate the median as an analytic function and then aggregate:

select group_id,
       avg(case when value < value_median then value end) as avg_below_median
from (select t.*,
             median(value) over (partition by group_id) as value_median
      from t
     ) t
group by group_id;

Note: You could filter using a where clause as well:

select group_id, avg(value) as avg_below_median
from (select t.*,
             median(value) over (partition by group_id) as value_median
      from t
     ) t
where value < value_median
group by group_id;

But the first method makes it simpler to add other expressions.