SELECT
MIN(DATE(s.receive_date)) order_date, YEAR(s.receive_date) y, MONTH(s.receive_date) m,
s.order_delivery_id,
s.`name`,
COUNT(s.id) kol,
SUM(IF(s.result in(9, 10), 1, 0)) 'Промоутеры',
SUM(IF(s.result in(7, 8), 1, 0)) 'Нейтралы',
SUM(IF(s.result BETWEEN 0 AND 6, 1,0)) 'Критики',
SUM(IF(s.result BETWEEN 0 AND 6, 1,0)) / COUNT(s.id) 'Доля критиков',
ROUND(SUM(IF(s.result in(9, 10), 1, IF(s.result in (7,8), 0, -1))) / COUNT(*), 2) nps
FROM (
SELECT n.*, o.order_delivery_id, od.`name`, COUNT(nr.order_id), GROUP_CONCAT(DISTINCT od.id), GROUP_CONCAT(DISTINCT o.id), MAX(IF(od.id = 1, 1, 0)) samovivoz, MAX(IF(od.id = 2, 1, 0)) courier, MAX(IF(od.id = 3, 1, 0)) nova_poshta
FROM nps_result n
LEFT JOIN nps_relations nr ON n.nps_polls_id = nr.nps_polls_id
LEFT JOIN orders o ON nr.order_id = o.id
LEFT JOIN order_delivery od ON o.order_delivery_id = od.id
WHERE
	n.result >= 0
	AND n.order_id IS NULL
	AND o.order_delivery_id NOT IN (4, 5, 6)
	AND DATE(o.date_create) >= '2017-09-01'
-- 	AND DATE(n.receive_date) = '2019-10-21'
GROUP BY n.id
) s
GROUP BY YEAR(s.receive_date), MONTH(s.receive_date)

UNION ALL

SELECT
MIN(DATE(nr.receive_date)) order_date, YEAR(nr.receive_date), MONTH(nr.receive_date),
o.order_delivery_id,
od.`name`,
COUNT(nr.order_id) kol,
SUM(IF(result in(9, 10), 1, 0)) 'Промоутеры',
SUM(IF(result in(7, 8), 1, 0)) 'Нейтралы',
SUM(IF(result BETWEEN 0 AND 6, 1,0)) 'Критики',
SUM(IF(result BETWEEN 0 AND 6, 1,0)) / COUNT(nr.order_id) 'Доля критиков',
ROUND(SUM(IF(result in(9, 10), 1, IF(result in (7,8), 0, -1))) / COUNT(*), 2) nps
FROM nps_result nr
LEFT JOIN orders o ON nr.order_id = o.id
LEFT JOIN order_delivery od ON o.order_delivery_id = od.id
WHERE o.order_delivery_id NOT IN (4, 6, 7) AND DATE(nr.receive_date) >= '2017-09-01' AND nr.order_id IS NOT NULL
GROUP BY YEAR(nr.receive_date), MONTH(nr.receive_date)

ORDER BY y, m