SQLize | PHPize | SQLtest

with RECURSIVE dat as ( select '2005-07-01' as date union all select date_add(date,interval 1 day) from dat where date<'2005-07-31'), t as (select date(rental_date) as date, store_id, count(rental_date) cre from inventory join rental using(inventory_id) group by 1,2), d as (select date(return_date) as date, store_id, count(return_date) cru from inventory join rental using(inventory_id) group by 1,2) select dat.date, coalesce(t1.cre,0) rented_at_store_1 , coalesce(d1.cru,0) returned_at_store_1 , coalesce(t2.cre,0) rented_at_store_2 , coalesce(d2.cru,0) returned_at_store_2, coalesce(t1.cre,0)+coalesce(t2.cre,0) as total_rented, coalesce(d1.cru,0)+coalesce(d2.cru,0) as total_returned from dat left join (select * from t where store_id=1) as t1 using(date) left join (select * from t where store_id=2) as t2 using(date) left join (select * from d where store_id=1) as d1 using(date) left join (select * from d where store_id=2) as d2 using(date) order by 1 ; show status like 'Last_query_cost';