Как просуммировать данные из двух таблиц?

Question

[artem78]

Есть два запроса, которые получают из двух разных таблиц статистические данные одного вида.

Первый:

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.

Answer 1

[Александр Шелеметьев]

Раз данные одного вида, то через UNION:

SELECT
`campaign_post`,
`campaign_name`,
`post`,
 COUNT(*) AS `leads`,
 SUM(`new`) AS `new`,
 SUM(`approved`) AS `approved`,
 SUM(`declined`) AS `declined`,
 SUM(`webmaster_profit`) AS `webmaster_profit`
FROM (
SELECT
  CONCAT(w,'|',t) AS `campaign_post`,
  w AS `campaign_name`,
  t AS `post`,
  IF(`status` = 'new', 1, 0) AS `new`,
  IF(`status` = 'approved', 1, 0) AS `approved`,
  IF(`status` = 'declined', 1, 0) AS `declined`,
  web_total AS `webmaster_profit`
FROM `m1`
UNION ALL
SELECT
  CONCAT(`subid1`, '|', `subid2`) AS `campaign_post`,
  `subid1` AS `campaign_name`,
  `subid2` AS `post`,
  IF(`status` = 'waiting', 1, 0) AS `new`,
  IF(`status` = 'approved', 1, 0) AS `approved`,
  IF(`status` IN ('declined', 'trash'), 1, 0) AS `declined`,
  `profit` AS `webmaster_profit`
FROM `add1`
) q1
GROUP BY `campaign_post`,
`campaign_name`,
`post`;

UPD. Исправил кавычки

Comments

[artem78]

Поле leads считает правильно, а в new, approved, declined и webmaster_profit по нулям.

[artem78]

У вас кавычки не те стоят в SUM('new') ... SUM('webmaster_profit').