Getting Grouped Data Mysql

Question

I have three schemas

tbl_routes.       tbl_alloc         tbl_alloc_items
-id,name.         - id, date.       id, quantity, alloc_id, route_id  

I am trying to query to get sum of quantity in say a month using tbl_alloc.date but group by route name, ie get the sum of every route in a month.

am mentally stuck on this , anyone with a fresh eyes to help

my current attempt on this is as follows

SELECT r.name, sum(i.quantity) as target 
from tbl_alloc_items i 
    left join tbl_alloc a on i.alloc_id = a.id 
    left join tbl_routes r on i.route_id = r.id 
where date between '2022-10-01' and '2022-10-31' 
group by i.route_id;

the aim is to get data like following

Answer 1

I am posting how I have solved it. This is how

   SELECT tbl_routes.name, SUM(tbl_alloc_items.quantity)
   FROM tbl_alloc
   JOIN tbl_alloc_items ON tbl_alloc.id = tbl_alloc_items.alloc_id
   JOIN tbl_routes ON tbl_alloc_items.route_id = tbl_routes.id
   WHERE MONTH(tbl_alloc.date) = '{month}'
   GROUP BY tbl_routes.name