Есть два запроса, которые получают из двух разных таблиц статистические данные одного вида.
Первый:
SELECT
CONCAT(w,'|',t) AS `campaign_post`,
w AS `campaign_name`,
t AS `post`,
COUNT(*) AS `leads`,
SUM(IF(`status` = 'new', 1, 0)) AS `new`,
SUM(IF(`status` = 'approved', 1, 0)) AS `approved`,
SUM(IF(`status` = 'declined', 1, 0)) AS `declined`,
SUM(web_total) AS `webmaster_profit`
FROM `m1`
GROUP BY `campaign_post`;
+---------------+---------------+-------+-------+------+----------+----------+------------------+
| campaign_post | campaign_name | post | leads | new | approved | declined | webmaster_profit |
+---------------+---------------+-------+-------+------+----------+----------+------------------+
| EMPTY|EMPTY | EMPTY | EMPTY | 1 | 0 | 1 | 0 | 650.00 |
| EMPTY|vkads | EMPTY | vkads | 1 | 0 | 0 | 1 | 500.00 |
| M1_1243|11 | M1_1243 | 11 | 2 | 1 | 0 | 1 | 1200.00 |
| M1_1243|41 | M1_1243 | 41 | 3 | 1 | 2 | 0 | 1700.00 |
| M1_1243|47 | M1_1243 | 47 | 1 | 0 | 1 | 0 | 600.00 |
| M1_1243|EMPTY | M1_1243 | EMPTY | 1 | 0 | 0 | 1 | 600.00 |
+---------------+---------------+-------+-------+------+----------+----------+------------------+
Второй:
SELECT
CONCAT(`subid1`, '|', `subid2`) AS `campaign_post`,
`subid1` AS `campaign_name`,
`subid2` AS `post`,
COUNT(*) as `leads`,
SUM(IF(`status` = 'waiting', 1, 0)) AS `new`,
SUM(IF(`status` = 'approved', 1, 0)) AS `approved`,
SUM(IF(`status` IN ('declined', 'trash'), 1, 0)) AS `declined`,
SUM(`profit`) AS `webmaster_profit`
FROM `add1`
GROUP BY `campaign_post`;
+---------------+---------------+------+-------+------+----------+----------+------------------+
| campaign_post | campaign_name | post | leads | new | approved | declined | webmaster_profit |
+---------------+---------------+------+-------+------+----------+----------+------------------+
| CAMP1|3 | CAMP1 | 3 | 1 | 0 | 1 | 0 | 560.00 |
| CAMP1|4 | CAMP1 | 4 | 1 | 0 | 0 | 1 | 590.00 |
| CAMP1|9 | CAMP1 | 9 | 1 | 0 | 0 | 1 | 660.00 |
| M1_1243|11 | M1_1243 | 11 | 3 | 1 | 1 | 1 | 1640.00 |
| M1_1243|41 | M1_1243 | 41 | 4 | 2 | 0 | 2 | 2300.00 |
+---------------+---------------+------+-------+------+----------+----------+------------------+
Эти две таблицы нужно объединить в одну. При этом если есть строки с одинаковым значением
campaign_name + post (
campaign_post), для них значения столбцов
leads,
new,
approved,
declined и
webmaster_profit нужно просуммировать. Т.е. результат должен быть следующий:
+---------------+---------------+-------+-------+------+----------+----------+------------------+
| campaign_post | campaign_name | post | leads | new | approved | declined | webmaster_profit |
+---------------+---------------+-------+-------+------+----------+----------+------------------+
| CAMP1|3 | CAMP1 | 3 | 1 | 0 | 1 | 0 | 560.00 |
| CAMP1|4 | CAMP1 | 4 | 1 | 0 | 0 | 1 | 590.00 |
| CAMP1|9 | CAMP1 | 9 | 1 | 0 | 0 | 1 | 660.00 |
| M1_1243|11 | M1_1243 | 11 | 5 | 2 | 1 | 2 | 2840.00 |
| M1_1243|41 | M1_1243 | 41 | 7 | 3 | 2 | 2 | 4000.00 |
| EMPTY|EMPTY | EMPTY | EMPTY | 1 | 0 | 1 | 0 | 650.00 |
| EMPTY|vkads | EMPTY | vkads | 1 | 0 | 0 | 1 | 500.00 |
| M1_1243|47 | M1_1243 | 47 | 1 | 0 | 1 | 0 | 600.00 |
| M1_1243|EMPTY | M1_1243 | EMPTY | 1 | 0 | 0 | 1 | 600.00 |
+---------------+---------------+-------+-------+------+----------+----------+------------------+
Каким запросом это можно сделать? Подозреваю что это возможно через Join.
