SQLize | PHPize | SQLtest

with recursive cte as ( select '2005-07-01' as date union all select date + interval 1 day from cte where date < '2005-07-31' ), st1_rent as ( select date_format(r.rental_date, '%Y-%m-%d') as date, count(r.rental_date) as cnt from rental r join inventory i on i.inventory_id = r.inventory_id where i.store_id = 1 group by date), st1_ret as ( select date_format(r.return_date, '%Y-%m-%d') as date, count(r.return_date) as cnt from rental r join inventory i on i.inventory_id = r.inventory_id where i.store_id = 1 group by date), st2_rent as ( select date_format(r.rental_date, '%Y-%m-%d') as date, count(r.rental_date) as cnt from rental r join inventory i on i.inventory_id = r.inventory_id where i.store_id = 2 group by date), st2_ret as ( select date_format(r.return_date, '%Y-%m-%d') as date, count(r.return_date) as cnt from rental r join inventory i on i.inventory_id = r.inventory_id where i.store_id = 2 group by date) select cte.date, coalesce(st1_rent.cnt,0) as rented_at_store_1, coalesce(st1_ret.cnt,0) as returned_at_store_1, coalesce(st2_rent.cnt,0) as rented_at_store_1, coalesce(st2_ret.cnt,0) as returned_at_store_2, coalesce(st1_rent.cnt,0) + coalesce(st2_rent.cnt,0) as total_rented, coalesce(st1_ret.cnt,0) + coalesce(st2_ret.cnt,0) as total_returned from cte left join st1_rent on st1_rent.date = cte.date left join st2_rent on st2_rent.date = cte.date left join st1_ret on st1_ret.date = cte.date left join st2_ret on st2_ret.date = cte.date ; show status like 'Last_query_cost';