Reputation: 2851
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
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
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
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