0

I have two tables - PRODUCT, ACTIVITY. Each product can have multiple activity (1:n). Each activity has an (INT) action column. I need to query all the products and the SUM(product.action) of the 10 most recent activities.

My first attempt was to use a sub-query:

select p.*, (select sum(a.action) from activity a 
        where a.product_id = p.product_id
        order by a.timestamp desc limit 10) recent
from product p

However, the result was incorrect. I realized that the sub-query wasn't using the LIMIT and was returning the SUM() of all ACTIVITY records matching the product_id.

My second attempt was to follow the advice here and wrap the sub-query in another select query: 

select p.*, (select sum(temp.action) as recent
from (select a.action from activity a 
        where a.product_id = p.product_id 
        order by a.timestamp desc limit 10) temp)
from product p

However, I got the error message Error Code: 1054. Unknown column 'p.product_id' in 'where clause'. I found a related question here and realized that MYSQL doesn't support alias on 2nd level nesting. I didn't quite follow the answer for that question.

I also tried an outer join 

select p.*, sum(temp.action) as recent
from product p
left join
(select a.product_id, a.action from activity a 
        where a.product_id = p.product_id
        order by a.timestamp desc limit 10) temp
on p.product_id= temp.product_id

Again, I ran into the same issues:

  1. The LIMIT is not enforced
  2. The alias is not recognized

How do I fix this?

Community
  • 1
  • 1
Bhardwaj
  • 535
  • 1
  • 4
  • 6

1 Answers1

0

1- Get distinct products from your product table
2- Get the ten most recent activities for each product
3- Get sums from (2)
4- Join

Take a look at Using LIMIT within GROUP BY to get N results per group?. It sounds similar to what you need for (2).

EDIT

I modified the query slightly and tested it on a small dataset. The previous version did not work because the where clause was in the wrong place. See below.

select t.product_id, sum(t.action) from
(
    select product_id, action, timestamp, 
    @row_num := if(@prev = product_id, @row_num + 1, 1) as row_num, @prev := product_id 
    from activity
    join (select @prev := null, @row_num := 0) as vars
    order by product_id, timestamp desc
) as t
where t.row_num <= 10
group by t.product_id;
Community
  • 1
  • 1
geco17
  • 5,152
  • 3
  • 21
  • 38
  • I tried this and ran into Error Code: 1054. Unknown column 'p.product_id' in 'where clause'. I've updated the question with the query. Does the query match what you had in mind? – Bhardwaj Jan 14 '16 at 04:17
  • Have you tried anything with variables? I wasn't aware of the subquery problem and updated my answer accordingly. – geco17 Jan 14 '16 at 13:15
  • Thanks for the query. Unfortunately, it returns the SUM() of all activity records for that product_id, and doesn't limit it to the 10 most recent. For product_id=3484, I have 16 records in the ACTIVITY table (each with action=1). The result should return the sum of the 10 most recent records, which is 10. However, it returns 16. – Bhardwaj Jan 14 '16 at 15:11
  • Thanks fro your feedback. I made a correction to the post, have a look. I think I've got it right now. – geco17 Jan 14 '16 at 19:18
  • You nailed it. Thank you very much! – Bhardwaj Jan 14 '16 at 23:59