Get total amount of fruits each mammal is carring

Get total amount of fruits each mammal is carring

I am struggling with specific MySQL query based problem.
I have such result set:
Table: Report
mammal_id mammal_name fruit_name gets | total |
3 rabbit apple 4 | 5 |
3 rabbit carrot 4 | 4 |
3 rabbit cabbage 1 | 3 |
2 squirrel nuts 1 | 3 |
2 squirrel cabbage 2 | 2 |
1 chipmunk nuts 2 | 2 |
1 chipmunk apple 1 | 1 |
And I want to filter like this:
Table: Filtered
mammal_id mammal_name fruit_name has
3 rabbit apple 4
3 rabbit carrot 4
3 rabbit cabbage 1
2 squirrel nuts 1
2 squirrel cabbage 2
1 chipmunk nuts 2
1 chipmunk apple 1
The hole point is to get total amount of fruits each mammal is carring.
Now I have:
SELECT a.mammal_id, b.mammal_id, a.mammal_name, b.mammal_name,
a.fruit_name, b.fruit_name, (b.total - a.total) as has
FROM (SELECT * FROM Report (result set)) as a
INNER JOIN (SELECT * FROM Report (result set)) as b
ON a.fruit_name=b.fruit_name WHERE a.mammal_id = b.mammal_id-1
After this query, I get result like this:
Table: Result
a.mammal_id b.mammal_id a.mammal_name b.mammal_name a.fruit_name
b.fruit_name has
2 3 squirrel rabbit cabbage
cabbge 1
1 2 chipmunk squirrel nuts
nuts 1
Appreciate any guidance on this problem.