How to average last amount of last 5 transactions

Question

I have two transaction table and a product table. The product table has a column called "product". The transaction table has a column called "amount". I want to get the average of the last 5 transaction for each product. I have this so far, but can't figure out a statement to make it grab the only the last 5 of each distinct product:

SELECT avg(amount) as price, p.product_name, p.producer
FROM wp_transactions t
JOIN wp_products p
ON t.product_ID = p.ID
WHERE t.status = 'approved'
GROUP BY p.product_name
ORDER BY p.producer ASC, p.product_name ASC;

Answer 1

You can do this by enumerating the rows usig variables:

SELECT avg(amount) as price, p.product_name, p.producer
FROM (SELECT t.*,
             (@rn := if(@p = t.Product_Id, @rn + 1,
                        if(@pr := t.Product_Id, 1, 1)
                       )
             ) as seqnum
      FROM wp_transactions t CROSS JOIN
           (SELECT @rn := 0, @p := 0) vars
      WHERE t.status = 'approved'
      ORDER BY t.product_ID, t.datetime
     ) t JOIN
     wp_products p
     ON t.product_ID = p.ID
WHERE seqnum <= 5
GROUP BY p.product_name
ORDER BY p.producer ASC, p.product_name ASC;