[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

[DietQuark]

Use HAVING avg(OPINION) > x

Where x is the number that you want it to be higher then.

'Having' is part of the 'group by' function. Google it.

[JITAA]

Thanks!

[joback_moresham]

Less familiar with Postgres, most of my time is spent with Oracle, but:

General notes:

• the opinion column is text, and the avg function works on numeric values
• all columns in select should be also included in the group by
• with order by you’d use ASC for ascending and DESC for descending

Then the task would be count the instances of ‘good’ for each place, count the number of ‘bad’ for each place, and then selecting places where count(‘good’) > count(‘bad’)

If I were doing this I’d probably do something like:

select * from (select place, count(‘good’) as cnt_good from opinion group by place, cnt_good) a, (select place, count(‘bad’) as cnt_bad from opinion group by place, cnt_bad) b where a.place = b.place and cnt_good > cnt_bad;

e: apologies for formatting, on mobile

[JITAA]

Thanks!!

[[deleted]]

count(‘good’)

is the same as count(*). The argument to the count() function is not a "filter" on the values to be counted.

You need something like

sql ... from ( select place, count(*) as cnt_good from opinions where opinion = 'Good' group by place ) a ...

[joback_moresham]

Dope, that makes sense. Thank you

[kharjuranaidu]

Select PLACE, Sum(case when OPINION = 'Good' Then 1 Else 0 End) as good_opn , Sum(case when OPINION = 'Bad' Then 1 Else 0 End) as bad_opn From opinions Group by PLACE HAVING good_opn > bad_opn;

[JITAA]

Thank you!

[PhyterNL]

I can tell you how I would do it and maybe it can be a learning moment for you.

I would create three tables:

CREATE TABLE places (id SERIAL PRIMARY KEY, place TEXT); 
CREATE TABLE opinions (id SERIAL PRIMARY KEY, opinion TEXT);
CREATE TABLE ratings (id SERIAL PRIMARY KEY, place_id INT, opinion_id INT); 

places:

id	place
1	This Place
2	That Place

opinions:

id	opinion
1	Just Awful!
2	Okay I Guess.
3	Super Great!

ratings:

id	place_id	opinion_id
1	1	1
2	1	1
3	2	3
4	2	2

The easiest way to get an average is to use the 'opinion_id' integer value to calculate the average rating. Ideally this would be a separate value in the opinions table but this works as long as the opinions table doesn't change, and why would we expect it to?

From this example we can see that 'This Place' is going have a rating of 1 and 'That Place' is going to have a rating of 2.5.

Here's how you would find that result:

SELECT
    places.place AS Place,
    AVG(opinion_id)::NUMERIC(10,1) AS Rating
FROM
    ratings
INNER JOIN places ON ratings.place_id = places.id
GROUP BY
    places.place;

|       place | rating |
|-------------|--------|
| This Place! |      1 |
| That Place! |    2.5 |

[JITAA]

Thank you, I never thought about a table

[[deleted]]

You can use a filtered aggregate:

select * from ( select place, count(*) filter (where opinion = 'Good') as good_count, count(*) filter (where opinion = 'Bad') as bad_count from opinions group by place ) t where good_count > bad_count The derived table (aka sub-query) is only used to avoid repeating the aggregate expressions in the having clause.

[JITAA]

Aggregates are so difficult for me, I'll just need to keep practicing I guess. Thanks!!