Compare values in two tables and calculate the difference of them

Question

DB-Fiddle

/* Table Sales */
CREATE TABLE sales (
    id int auto_increment primary key,
    product VARCHAR(255),
    KPI VARCHAR(255),
    sales_volume INT
);

INSERT INTO sales
(product, KPI, sales_volume)
VALUES 
("Product A", "sold", "500"),
("Product A", "sold", "300"),
("Product B", "sold", "200"),
("Product C", "sold", "300"),
("Product D", "sold", "900");


/* Table Logistics */
CREATE TABLE logistics (
    id int auto_increment primary key,
    product VARCHAR(255),
    KPI VARCHAR(255),
    quantity INT
);

INSERT INTO logistics
(product, KPI, quantity)
VALUES 
("Product A", "outbound", "800"),
("Product B", "outbound", "100"),
("Product B", "outbound", "400"),
("Product C", "outbound", "250"),
("Product D", "outbound", "900");

Expected Result:

product         value_in_sales      value_in_logistics      differnce_of_values
Product A        800                     800                       0
Product B        200                     500                       300
Product C        300                     250                       -50
Product D        900                     900                       0

---

In the example above I have two tables called sales and logistics.
My target ist to compare the sales_volume of the KPI sold from the table sales with the quantity of the KPI outbound from the table logistics.

I tried to go with UNION ALL but it only sorts the values of both tables below each other and does not compare them as in the expected result.

SELECT
product,
KPI,
SUM(sales_volume)
FROM sales
GROUP BY 1

UNION ALL

SELECT
product,
KPI,
SUM(quantity)
FROM logistics
GROUP BY 1

What do I need to change in my query to get the expected result?

Answer 1

You can do union all and then aggregate again:

SELECT product, SUM(sales) as sales, SUM(logistics) as logistics,
       (SUM(logistics) - SUM(sales)) as diff
FROM ((SELECT product, SUM(sales_volume) as sales, 0 as logistics
       FROM sales
       WHERE KPI = 'sold'
       GROUP BY product
      ) UNION ALL
      (SELECT product, 0, SUM(quantity)
       FROM logistics
       WHERE KPI = 'outbound'
       GROUP BY product
      )
     ) p
GROUP BY product;

This approach works even if the two tables do not have exactly the same products.

Answer 2

Use sub-query & do further aggregation :

SELECT Product, SUM(logistics),  SUM(Sales),
       SUM(logistics) - SUM(Sales)
FROM (SELECT product, 0 AS logistics, SUM(sales_volume) AS Sales
      FROM sales
      WHERE KPI = 'sold'
      GROUP BY Product
      UNION ALL
      SELECT product, SUM(quantity), 0 
      FROM logistics
      WHERE KPI = 'outbound'
      GROUP BY Product
     ) t
GROUP BY Product;