Hello,
My SQL skills are limited and I'm struggling with a query where I want to
return a single item of an aggregate join. The query looks like this:
select
(case
when agg.avg_rating is null then 0.0
when agg.avg_rating < 0.75 then 0.5
when agg.avg_rating < 1.25 then 1.0
when agg.avg_rating < 1.75 then 1.5
when agg.avg_rating < 2.25 then 2.0
when agg.avg_rating < 2.75 then 2.5
when agg.avg_rating < 3.25 then 3.0
when agg.avg_rating < 3.75 then 3.5
when agg.avg_rating < 4.25 then 4.0
when agg.avg_rating < 4.75 then 4.5
else 5.0
end) as avg_rating,
count(item.itemid) as item_count
from media_item item
inner join (
select rating.mediaitem_userrating_hjid as ritemid,
avg(rating.rating) as avg_rating
from media_item_rating rating, media_item item
where rating.mediaitem_userrating_hjid = item.itemid
group by rating.mediaitem_userrating_hjid
) as agg
on item.itemid = agg.ritemid
group by avg_rating
order by avg_rating desc
and a sample of results is this:
avg_rating | item_count
------------+------------
5.0 | 21
4.0 | 33
3.0 | 13
2.0 | 4
1.0 | 1
What I want as well is the ID of the item (and possibly it's avg_rating
value) from the "agg" join with the highest avg_rating for each output
row... something like this
avg_rating | item_count | item_id | item_rating
------------+-----------------------------------
5.0 | 21 | 109890 | 4.9
4.0 | 33 | 89201 | 4.1
3.0 | 13 | 119029 | 2.8
2.0 | 4 | 182999 | 2.2
1.0 | 1 | 1929 | 1.0
So the intention in this example is that item #109890 has an average
rating of 4.9 and that is the highest rating within the > 4.75 rating
group.
If anyone had any tips I'd greatly appreciate it.
-- m@
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
