CODEWITHSUNDEEP
mysqlsql
frosty
frosty

Reputation: 2851

Sort by average rating between 2 values

I have a table called ranks which stores a 1-7 value and connects it with an item and user id. An item's rating is the average of all ratings it has been given.

Ranks table: id | userid | value | itemid.

To sort by average highest rating, I do (from an answer here):

select avg(value), itemid
from ranks
group by itemid
order by avg(value) desc

However, I also want to be able to filter items with a certain rating, not just highest rated -> lowest rated. For example, items with an average rating beteen 5 and 6.

select avg(value), itemid
from ranks
having avg(value) between 5 and 6
group by itemid
order by avg(value) desc

This gives me a nonspecific error at group by. Can anyone point me in the right direction?

Upvotes: 1

Views: 81

Answers (3)

FuzzyTree
FuzzyTree

Reputation: 32402

Your having clause needs to come after your group by clause, so just reverse the order they appear in:

select avg(value), itemid
from ranks
group by itemid
having avg(value) between 5 and 6
order by avg(value) desc

Upvotes: 1

Ali Adravi
Ali Adravi

Reputation: 22833

with Temp as 
(
select avg(value) [rating], itemid
from ranks
)
Select * from Temp
Where rating between 5 and 6
group by itemid
order by rating desc

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

select * from
(
select avg(value) as avgval, itemid
from ranks
group by itemid) t
where avgval between 5 and 6;

You can do it with a sub-query.

Upvotes: 1

Related Questions