[jablan]

Može i vako:

# select * from test;

 group_id | flag 
----------+------
        1 | no
        1 | yes
        2 | no
        2 | no
        1 | no
(5 rows)

# select t1.group_id, coalesce(t2.flag,'no') as f
from test t1
left join test t2 on t1.group_id = t2.group_id and t2.flag = 'yes'
group by t1.group_id, f;

 group_id |  f  
----------+-----
        1 | yes
        2 | no
(2 rows)