CODEWITHSUNDEEP
mysql
Spin Rodriguez
Spin Rodriguez

Reputation: 63

MySQL: Find the average value per entry for the last x records

I'm trying to figure out how to grab the average rating for each salesperson over their last 100 ratings if they are currently employed, and if they have an average rating less than 3 (out of 5).

I have the following tables (leaving out information that isn't needed in the query):

users
id    name     employed
-----------------------
1     John     1
2     Sue      1
3     Bob      0
...

sales
id     users_id
------------------
100    3
101    2
102    3
103    1
...

ratings
sales_id   rating
-----------------
100        4
101        5
102        5
103        2
...

The current query I have searches everything and returns the average for all orders ever but I want it to only grab the most recent 100 ratings (or less if the salesperson hasn't sold that many items), still excluding anyone that is no longer employed or has a rating for their last 100 orders greater than 3. This is the current query:

SELECT u.name, avg(r.rating) as avg_rating, count(r.rating)
FROM users AS u
JOIN sales AS s ON s.users_id = u.id
JOIN ratings AS r ON r.sales_id = s.id
WHERE u.employed = 1
GROUP BY u.id
HAVING avg_rating <= 3;

Any help would be great! Thanks! :D

Upvotes: 2

Views: 130

Answers (1)

radar
radar

Reputation: 13425

You can use my sql variables to keep track of the number of ratings so that you can get only recent 100 ratings , ordering by sales_id so you get recent ratings.

SQL FIDDLE DEMO

SELECT T.name, avg(T.rating) as avg_rating, count(T.rating)
FROM
(
  SELECT u.name, r.rating, @num := if (@name = name, @num+1, 1) as rn,
       @name:= name as var_name
  FROM users AS u
  JOIN sales AS s ON s.users_id = u.id
  JOIN ratings AS r ON r.sales_id = s.id
  AND u.employed = 1
  JOIN ( select @name :='' , @num :=1) var
  order by sales_id desc
)T
where T.rn <=100
GROUP BY T.name
HAVING avg_rating <= 3

Upvotes: 2

Related Questions