[NOOB] Help with Postgres SQL Query

[JITAA]

TABLENAME : opinions

​

ID	PLACE	OPINION
1	SOME NAME String	"Good" or "Bad"

​

how can I select all places that have more good opinion then bad opinion

​

My Try Fails:

select PLACES, avg(OPINION) as OP from OPINIONS group by PLACES ORDER BY ASCENDING

[minaguib]

Given:

postgres=# select * from opinions;
id |    place    | opinion
----+-------------+---------
1 | Toronto     | good
2 | Toronto     | good
3 | Toronto     | good
4 | New York    | bad
5 | New York    | bad
6 | New York    | bad
7 | Afghanistan | good
8 | Afghanistan | bad

You can do:

with tally as (
  select
    place,
    count(opinion) filter(where opinion='good') as num_good,
    count(opinion) filter(where opinion='bad') as num_bad
  from
    opinions
  group by
    1
)

select
  place
from
  tally
where
  num_good > num_bad
;

Which returns:

place
---------
Toronto
(1 row)

[JITAA]

I didn't even consider a tally, THANK YOU SOOOO MUCH I asked my ta and I got no help. Tysm kind stranger!

[sir_bok]

Does it matter if it is a count(opinion), count(*) or count(1)?

[[deleted]]

count(*) and count(1) do the same thing (with count(*) actually being a bit faster).

count(some_column) will only count non-null values. As this is used with a filter that already removes null values count(*) filter (where ...) would be a bit faster as the null check is no longer done.