SQLize Online / PHPize Online  /  SQLtest Online

/*SELECT store_id, (SELECT IF(SUM(amount) IS NULL,0,SUM(amount)) FROM payment p WHERE p.staff_id = stf.staff_id AND payment_date REGEXP '2005[-][0][123]') AS I, (SELECT IF(SUM(amount) IS NULL,0,SUM(amount)) FROM payment p WHERE p.staff_id = stf.staff_id AND payment_date REGEXP '2005[-][0][456]') AS II, (SELECT IF(SUM(amount) IS NULL,0,SUM(amount)) FROM payment p WHERE p.staff_id = stf.staff_id AND payment_date REGEXP '2005[-][0][789]') AS III, (SELECT IF(SUM(amount) IS NULL,0,SUM(amount)) FROM payment p WHERE p.staff_id = stf.staff_id AND payment_date REGEXP '2005[-][1][012]') AS IV FROM store s INNER JOIN staff stf USING(store_id)*/ /*SELECT DISTINCT store_id, IF(flag_1 = 1, I, IF(LEAD(flag_1) OVER (PARTITION BY store_id) = 1, LEAD(I) OVER (PARTITION BY store_id), IF(LAG(flag_1) OVER(PARTITION BY store_id) = 1, LAG(I) OVER(PARTITION BY store_id), 0))) AS I, IF(flag_2 = 1, II, IF(LEAD(flag_2) OVER (PARTITION BY store_id) = 1, LEAD(II) OVER (PARTITION BY store_id), IF(LAG(flag_2) OVER(PARTITION BY store_id) = 1, LAG(II) OVER(PARTITION BY store_id), 0))) AS II, IF(flag_3 = 1, III, IF(LEAD(flag_3) OVER (PARTITION BY store_id) = 1, LEAD(III) OVER (PARTITION BY store_id), IF(LAG(flag_3) OVER(PARTITION BY store_id) = 1, LAG(III) OVER(PARTITION BY store_id), 0))) AS III, IF(flag_4 = 1, IV, IF(LEAD(flag_4) OVER (PARTITION BY store_id) = 1, LEAD(IV) OVER (PARTITION BY store_id), IF(LAG(flag_4) OVER(PARTITION BY store_id) = 1, LAG(IV) OVER(PARTITION BY store_id), 0))) AS IV FROM (SELECT DISTINCT store_id, SUM(amount) OVER(PARTITION BY store_id, payment_date REGEXP '[-][0123]+[-]') AS I, IF(DATE_FORMAT(payment_date, "%m") IN (01, 02, 03), 1, 0) AS flag_1, SUM(amount) OVER(PARTITION BY store_id, payment_date REGEXP '[-][0456]+[-]') AS II, IF(DATE_FORMAT(payment_date, "%m") IN (04, 05, 06), 1, 0) AS flag_2, SUM(amount) OVER(PARTITION BY store_id, payment_date REGEXP '[-][0789]+[-]') AS III, IF(DATE_FORMAT(payment_date, "%m") IN (07, 08, 09), 1, 0) AS flag_3, SUM(amount) OVER(PARTITION BY store_id, payment_date REGEXP '[-][1][012][-]') AS IV, IF(DATE_FORMAT(payment_date, "%m") IN (10, 11, 12), 1, 0) AS flag_4 FROM payment p INNER JOIN staff s USING(staff_id) WHERE payment_date LIKE "2005-%") AS summs_at_months*/ WITH first_quart(store_id, payment) AS (SELECT store_id, SUM(amount) FROM staff s INNER JOIN payment p USING(staff_id) WHERE payment_date REGEXP "2005-[0123]+[-]" GROUP BY store_id), second_quart (store_id, payment) AS (SELECT store_id, SUM(amount) FROM staff s INNER JOIN payment p USING(staff_id) WHERE payment_date REGEXP "2005-[0456]+[-]" GROUP BY store_id), third_quart (store_id, payment) AS (SELECT store_id, SUM(amount) FROM staff s INNER JOIN payment p USING(staff_id) WHERE payment_date REGEXP "2005-[0789]+[-]" GROUP BY store_id), quart_quart (store_id, payment) AS (SELECT store_id, SUM(amount) FROM staff s INNER JOIN payment p USING(staff_id) WHERE payment_date REGEXP "2005-[1][012]+[-]" GROUP BY store_id) SELECT store_id, IFNULL(fq.payment, 0) AS I, IFNULL(sq.payment, 0) AS II, IFNULL(tq.payment, 0) AS III, IFNLL(qq.payment, 0) AS IV FROM store LEFT OUTER JOIN first_quart fq USING(store_id) LEFT OUTER JOIN second_quart sq USING(store_id) LEFT OUTER JOIN third_quart tq USING(store_id) LEFT OUTER JOIN quart_quart qq USING(store_id)