Skip to content
Advertisement

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

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:

enter image description here

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

Advertisement

Answer

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;
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement