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
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;