SQLize | PHPize | SQLtest

with recursive date as ( select date('20050701') as date UNION ALL select date+1 from date where date+1 <= date('20050731')) SELECT date, ifnull(sum(rented_at_store_1), 0) rented_at_store_1, ifnull(sum(returned_at_store_1), 0) returned_at_store_1, ifnull(sum(rented_at_store_2), 0) rented_at_store_2, ifnull(sum(returned_at_store_2), 0) returned_at_store_2, ifnull(sum(total_rented), 0) total_rented, ifnull(sum(total_returned), 0) total_returned FROM (SELECT date(rental_date) date1, count(CASE WHEN store_id = 1 THEN rental_date END) rented_at_store_1, count(CASE WHEN store_id = 2 THEN rental_date END) rented_at_store_2, count(rental_id) total_rented, 0 returned_at_store_1, 0 returned_at_store_2, 0 total_returned FROM rental JOIN inventory USING(inventory_id) WHERE date(rental_date) BETWEEN '2005-07-01' AND '2005-07-31' GROUP BY date(rental_date), store_id UNION SELECT date(return_date) date2, 0 rented_at_store_1, 0 rented_at_store_2, 0 total_rented, count(CASE WHEN store_id = 1 THEN return_date END) returned_at_store_1, count(CASE WHEN store_id = 2 THEN return_date END) returned_at_store_2, count(return_date) total_returned FROM rental JOIN inventory USING(inventory_id) WHERE date(return_date) BETWEEN '2005-07-01' AND '2005-07-31' GROUP BY date(return_date), store_id) b right join date on date = date1 GROUP BY date ORDER BY date; SHOW STATUS LIKE 'Last_query_cost';