SQLize | PHPize | SQLtest

with RECURSIVE month as( select date('2005-07-01') date union all select date + interval 1 day from month where date + interval 1 day <= '2005-07-31' ), rented as( select store_id ,date_format(rental_date, '%Y-%m-%d') date_ren ,count(rental_id) rented from rental r join inventory i on r.inventory_id = i.inventory_id group by store_id, date_ren ), returned as( select date_format(return_date, '%Y-%m-%d') date_ret ,store_id ,count(rental_id) returned from rental r left join inventory i on r.inventory_id = i.inventory_id group by date_ret, store_id ), s1 as( select date ,case when rented.rented is not null then rented.rented else 0 end rented_at_store_1 ,case when returned.returned is not null then returned.returned else 0 end returned_at_store_1 from month left join rented on month.date = rented.date_ren and rented.store_id = 1 left join returned on month.date = returned.date_ret and returned.store_id = 1 ), s2 as( select date ,case when rented.rented is not null then rented.rented else 0 end rented_at_store_2 ,case when returned.returned is not null then returned.returned else 0 end returned_at_store_2 from month left join rented on month.date = rented.date_ren and rented.store_id = 2 left join returned on month.date = returned.date_ret and returned.store_id = 2 ) select s1.date ,rented_at_store_1 ,returned_at_store_1 ,rented_at_store_2 ,returned_at_store_2 ,rented_at_store_1 + rented_at_store_2 total_rented ,returned_at_store_1 + returned_at_store_2 total_returned from s1 left join s2 on s2.date = s1.date ; show status like 'Last_query_cost';