Reputation: 11
The task asks me to use proc sql to find average rating given by each user that have reviewed more than 5 recipes. and datasets are below:
user_id recipe_id date rating
231 245 17/02/2019 20
098 134 31/01/2019 1
.......
.......
Could anyone show me how to get the result please?
Thank you very much
Upvotes: 1
Views: 623
Reputation: 44805
Simply do a GROUP BY
. Use HAVING
to only return users with more than different 5 recipes.
SELECT user_id, AVG(rating)
FROM tablename
GROUP BY user_id
HAVING COUNT(distinct recipe_id) > 5
Do COUNT(distinct recipe_id) > 5
to return only users with more than 5 different recipes reviewed. (As suggested by Serge.)
COUNT(recipe_id) > 5
could include same recipe several times.
Upvotes: 1
Reputation: 129
try this using sql join
SELECT
r.user_id
,AVG(rating) avg
FROM recipe r
inner join (
Select user_id
from recipe
group by user_id
having count(*) > 5
) c
on c.user_id =r.user_id
group by r.user_id
Upvotes: 0
Reputation: 28423
You should use Aggregate function effectively to achieve these.
Try Below
SELECT recipe_id, AVG(rating)
FROM Table1
WHERE user_id IN (SELECT user_id, COUNT(recipe_id) FROM Table1 GROUP BY user_id HAVING COUNT(recipe_id) > 5)
GROUP BY recipe_id
Upvotes: 1