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 all_income (store_id, payment_date, amount) AS (SELECT store_id, payment_date, amount FROM payment p INNER JOIN staff s USING(staff_id) WHERE payment_date LIKE '2005-%'), first_quart (store_id, sum1) AS (SELECT store_id, IFNULL(SUM(amount), 0) FROM all_income WHERE payment_date REGEXP '[-][0][123][-]' GROUP BY store_id), second_quart (store_id, sum2) AS (SELECT store_id, IFNULL(SUM(amount), 0) FROM all_income WHERE payment_date REGEXP '[-][0][456][-]' GROUP BY store_id), third_quart (store_id, sum3) AS (SELECT store_id, IFNULL(SUM(amount), 0) FROM all_income WHERE payment_date REGEXP '[-][0][789][-]' GROUP BY store_id), quart_quart (store_id, sum4) AS (SELECT store_id, IFNULL(SUM(amount), 0) FROM all_income WHERE payment_date REGEXP '[-][1][012][-]' GROUP BY store_id) SELECT sq.store_id, sum1 AS I, sum2 AS II, sum3 AS III, sum4 AS IV FROM first_quart fq INNER JOIN second_quart sq USING(store_id) INNER JOIN third_quart tq USING(store_id) INNER JOIN quart_quart qq USING(store_id)