CODEWITHSUNDEEP
mysqlsqlaverage
samleurs
samleurs

Reputation: 146

Select ID's where average rating between

I have 2 tables:

table "biz" and table "biz_rating"

biz

biz_rating

I want a query to select ALL biz rows where the average rating is between 1-2, or 3-4, or < 5 or > 5 (rating is a number from 0 to 5).

I have literally NO IDEA how to start.

I have something like this now:

SELECT
    biz_id
FROM
    biz
WHERE
    biz_id IN
    (SELECT biz_id FROM biz_rating WHERE AVG(rating) BETWEEN 0 AND 5)
ORDER BY
    biz_id ASC

but that's not working at all.

Upvotes: 1

Views: 130

Answers (2)

saumik gupta
saumik gupta

Reputation: 167

You can join the table if you want the name along with id

 select biz_id,name,b2.avgrating
 from BIZ as B1,
( select biz_id as bid, avg(rating) as avgrating
 from biz_rating
 group by biz_id 
 having avg(rating) between 0 and 5) as B2

 where B1.biz_id=B2.bid

This will also select the average rating if you want

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271191

Just use aggregation. You don't even need a join for this:

select br.biz_id
from biz_rating br
group by br.biz_id
having avg(rating) between 1 and 2;  -- or whatever limits you want

Upvotes: 0

Related Questions