Get the first n lines for group after summing up

Question

I am practising my skills in MySQL using the Sakila DB.

I would I have created a view called rentals_customer_store_film_category with is the union of customers, payments, rentals, film, and category tables.

I would now like to get the top 5 films by income. Meaning that I would lie to sum up all incomes of each film by store and then return the first 5. I tried the below code but it does not work. What is wrong with it?

SELECT  store_id, film_id, income
FROM
(SELECT film_id, store_id, sum(amount) as income, 
    @store_rank := IF(@current_store = store_id, @store_rank + 1, 1) AS store_rank,
    @current_store := store_id
FROM rentals_customer_store_film_category
group by store_id, film_id
ORDER BY store_id, income DESC, film_id
) ranked
WHERE store_rank <= 5

The results are below. As you can see, it does not stop at the fifth row per store. It shows all films by store while I would like only the top 5 for store:id 1 and top 5 for store id 2.

store_id film_id    income
1   971 134.82
1   879 132.85
1   938 127.82
1   973 123.83
1   865 119.84
1   941 117.82
1   267 116.83
1   327 110.85
1   580 106.86
1   715 105.85
1   897 104.85
...
...
...
...
2   878 127.83
2   791 127.82
2   854 123.83
2   946 117.86
2   396 113.81
2   369 111.84
2   764 110.85
2   260 110.84
2   838 110.82
2   527 109.83
2   893 106.84 
2   71  102.87
2   8   102.82
...
...
...
...

Answer 1

The order in this case is important to compare the previous store_id with the current,try this:

SELECT  store_id, film_id, income
FROM
(SELECT film_id, store_id, sum(amount) as income,
      #First compare previus with current
     @store_rank := IF(@prev_store = store_id, @store_rank + 1, 1) AS store_rank,
     #asign previus store
     @prev_store := store_id
FROM films
group by store_id, film_id
ORDER BY store_id, income DESC, film_id
) ranked
WHERE store_rank <= 5