Group and Sum from multiple tables in MySQL

Question

I would like to Group 2 tables Based on the First two columns. I have successfully done this and code can be seen below. I need some help to SUM the remaining column from each of the tables and INSERT into my output table with a renamed column as can bee seen below. In the output the FORECAST column is a result from the MI_STAT_TOTAL table and the SALES column is a result from the SALES_TOTAL table.

TABLE: MI_STAT_TOTAL

Planning_Hierarchy_2  Region_Description          Measure_Values
xx                    LATAM                        2   
yy                    ASPAC                        3
zz                    EMEA                         4
zz                    EMEA                         1
aa                    ASPAC                        1

TABLE: SALES_TOTAL

Product_Number        Region                      Item_Values
xx                    LATAM                        1  
yy                    ASPAC                        2
zz                    EMEA                         2
zz                    EMEA                         1
bb                    USA                          2

EXPECTED OUTPUT TABLE: P2_Region_Unique

Product_Number        Region                      Forecast       Sales
xx                    LATAM                        2             1
yy                    ASPAC                        3             2
zz                    EMEA                         5             3 
aa                    ASPAC                        1
bb                    USA                                        2

Code below that considers only the GROUP BY between the two tables but not the SUM:

CREATE TABLE P2_Region_Unique (
  Product_Number VARCHAR(100),
  Region VARCHAR(13)
  );

INSERT INTO P2_Region_Unique
    SELECT s.Planning_Hierarchy_2 AS Product_Number, s.Region_Description` AS Region
    FROM MI_STAT_TOTAL s
    UNION
    SELECT m.`Product Number` AS `Product_Number`, m.`Region` AS Region
    FROM SALES_TOTAL m;

EXPECTED OUTPUT TABLE FORMAT considering that the SUM is also included

CREATE TABLE P2_Region_Unique (
  `Product Number` VARCHAR(100),
  `Region` VARCHAR(13),
  `Forecast` DECIMAL (20,10),
  `Sales` DECIMAL (20,10)
  );

Answer 1

In MySQL, you can turn a SELECT to an INSERT using the INSERT ... SELECT syntax.

You sample data indicates that you are dealing with a N-M join. The first thing to do is to move the aggregation into subqueries, as follows :

INSERT INTO P2_Region_Unique
SELECT s.product_number, s.region, f.forecast, s.sales
FROM
    (
        SELECT product_number, region, SUM(item_values) sales
        FROM SALES_TOTAL
        GROUP BY product_number, region
    ) s
    LEFT JOIN (
        SELECT planning_hierarchy_2 product_number, region_description region, SUM(measure_values) forecast
        FROM MI_STAT_TOTAL
        GROUP BY planning_hierarchy_2, region_description
    ) f ON f.product_number = s.product_number AND f.region = s.region
GROUP BY s.product_number, s.region

This will work but will not return records from both ends of the join. For that, you can emulate FULL OUTER JOIN (which, as pointed out by @Nick, is not supported in MySQL) with UNIONed queries, like :

INSERT INTO P2_Region_Unique
SELECT s.product_number, s.region, f.forecast, s.sales
FROM
    (
        SELECT product_number, region, SUM(item_values) sales
        FROM SALES_TOTAL
        GROUP BY product_number, region
    ) s
    LEFT JOIN (
        SELECT planning_hierarchy_2 product_number, region_description region, SUM(measure_values) forecast
        FROM MI_STAT_TOTAL
        GROUP BY planning_hierarchy_2, region_description
    ) f ON f.product_number = s.product_number AND f.region = s.region
GROUP BY s.product_number, s.region
UNION
SELECT f.product_number, f.region, f.forecast, s.sales
FROM
    (
        SELECT planning_hierarchy_2 product_number, region_description region, SUM(measure_values) forecast
        FROM MI_STAT_TOTAL
        GROUP BY planning_hierarchy_2, region_description
    ) f
    LEFT JOIN (
        SELECT product_number, region, SUM(item_values) sales
        FROM SALES_TOTAL
        GROUP BY product_number, region
    ) s ON f.product_number = s.product_number AND f.region = s.region
GROUP BY f.product_number, f.region

The UNION operator eliminates duplicates across subqueries.

This demo on DB Fiddle yields :

| product_number | region | forecast | sales |
| -------------- | ------ | -------- | ----- |
| aa             | ASPAC  | 1        |       |
| bb             | USA    |          | 2     |
| xx             | LATAM  | 2        | 1     |
| yy             | ASPAC  | 3        | 2     |
| zz             | EMEA   | 5        | 3     |

Answer 2

Try This

select 
  s.ProductNumber,
  s.Region,
  sum(measureValues) Forecast,
  sum(itemValues) Sales
from MI_STAT_TOTAL m
FULL OUTER join SALES_TOTAL s
  on m.PlanningHierarcy2 = s.ProductNumber
group by
  s.ProductNumber,
  s.Region

for insert operation

 INSERT INTO P2_Region_Unique
    select 
      s.ProductNumber,
      s.Region,
      sum(measureValues) Forecast,
      sum(itemValues) Sales
    from MI_STAT_TOTAL m
    FULL OUTER join SALES_TOTAL s
      on m.PlanningHierarcy2 = s.ProductNumber
    group by
      s.ProductNumber,
      s.Region

if your MySQL version is below Version 8 Then

select 
  s.ProductNumber,
  s.Region,
  sum(measureValues) Forecast,
  sum(itemValues) Sales
from 
(
  SELECT * from MI_STAT_TOTAL m
  LEFT join SALES_TOTAL s
    on m.PlanningHierarcy2 = s.ProductNumber
  union 
  SELECT * from MI_STAT_TOTAL m
  RIGHT JOIN SALES_TOTAL s
    on m.PlanningHierarcy2 = s.ProductNumber
)
group by
  s.ProductNumber,
  s.Region