SQLize | PHPize | SQLtest

with recursive dates as ( select '2005-07-01' as dt union all select dt + interval 1 day from dates where dt < '2005-07-31' ), t0 as ( select store_id , date(rental_date) as rental_date , date(return_date) as return_date from inventory i join rental r on r.inventory_id = i.inventory_id where r.rental_date >= date'2005-07-01' and r.rental_date < date'2005-08-01' or r.return_date >= date'2005-07-01' and r.return_date < date'2005-08-01' ), ren as ( select rental_date , count(case when store_id = 1 then 1 end) as c1 , count(case when store_id = 2 then 1 end) as c2 , count(*) as ct from t0 where rental_date >= date'2005-07-01'and rental_date < date'2005-08-01' group by rental_date ), ret as ( select return_date , count(case when store_id = 1 then 1 end) as r1 , count(case when store_id = 2 then 1 end) as r2 , count(*) as rt from t0 where return_date >= date'2005-07-01'and return_date < date'2005-08-01' group by return_date ) select d.dt as date , ifnull(c.c1,0) as rented_at_store_1 , ifnull(r.r1,0) as returned_at_store_1 , ifnull(c.c2,0) as rented_at_store_2 , ifnull(r.r2,0) as returned_at_store_2 , ifnull(c.ct,0) as total_rented , ifnull(r.rt,0) as total_returned from dates d left join ren c on c.rental_date = d.dt left join ret r on r.return_date = d.dt ;; SHOW STATUS LIKE 'Last_query_cost';