How to choose in postgresql rows where amount of one value is bigger than another?

Question

how to choose in postgresql rows where amount of one value is bigger than another? For example I need to choose only those where recommended is bigger:

In other words:

create table t(id bigint, place text, opinion text);
insert into t values
    (1, 'mnt', 'yes'),
    (2, 'mnt', 'no'),
    (3, 'mnt', 'no'),
    (4, 'cod', 'yes'),
    (5, 'cod', 'yes'),
    (6, 'cod', 'yes'),
    (7, 'qrw', 'yes'),
    (8, 'qrw', 'no'),
    (9, 'caf', 'no'),
    (10, 'mnt', 'yes');

I tried to count group by place, opinion

Answer 1

If you are looking for places where recommended is bigger than not recommended, you can use aggregation:

select place
from t
group by place
having count(*) filter (where opinion = 'recommended') > count(*) filter (where opinion = 'not recommended');

You can also express this a bit more simply as:

select place
from t
where opinion in ('recommended', 'not recommended')
group by place
having avg( (opinion = 'recommended)::int ) > 0.5;