LEFT JOIN of two tables, SUM, GROUP

Question

I've got two tables, INPUT and OUTPUT.

INPUT table with columns ID, productName, QTY, buyPrice, sellPrice. OUTPUT table with columns ID, productName, QTY. Both can have multiple inputs with same productName and different (or same) QTY. OUTPUT can't have productName that doesn't appear in INPUT table first.

I'm trying to query both tables and to get the end result - something like this: productName, SUM(QTY) from INPUT table (GROUP BY productName), productName, SUM(QTY) from OUTPUT (GROUP BY productName)

Example:

INPUT

ID | productName | QTY | buyPrice | sellPrice
1  |  dress 007  |  2  |   700    |   1400
2  |  shirt 001  |  4  |   800    |   1900
3  |  dress 007  | 10  |   700    |   1500
4  |  dress 007  |  6  |   900    |   2900
5  |  shirt 001  |  2  |   750    |   1600
6  |    hat 008  |  2  |   300    |    600

OUTPUT

ID | productName | QTY
1  |  dress 007  |  4
2  |  shirt 001  |  2
3  |  dress 007  |  1
4  |  dress 007  |  1
5  |  shirt 001  |  3

RESULT SHOULD BE

productName | SUM(QTY)INPUT | productName | SUM(QTY)OUTPUT
 dress 007  |      18       |  dress 007  |        6
 shirt 001  |       6       |  shirt 001  |        5
   hat 008  |       2       |     null    |       null

It's easy to do the SUM and GROUP when it's only one table, but when I try to LEFT JOIN both tables and GROUP by productName I get wrong values for SUMs. Where am I mistaken?

Easy part:

SELECT a.productName , SUM(a.QTY), a.buyPrice , a.sellPrice
FROM input a
GROUP BY a.productName

Error:

SELECT a.productName , SUM(a.QTY), b.productName , SUM(b.QTY)
FROM input a LEFT JOIN output b
ON a.productName = b.productName 
GROUP BY a.productName

Answer 1

You could use a union all:

SELECT  IF(sum(input) , productName, null) productName, sum(input) input,
        IF(sum(output), productName, null) productName, sum(output) output
FROM    (
            SELECT productName, QTY input, null output
            FROM input
            UNION ALL
            SELECT productName, null, QTY
            FROM output
        ) as sub
GROUP BY productName

sqlfiddle

This will even work in the situation where you have output for a product, but no input. This can happen when the data concerns transactions in -- let's say -- one month, but where there is already a stock at the start of that month.

Answer 2

I'd do the aggregation in subqueries and join them.

SELECT input.productname,
       input.quantity input_quantity,
       output.quantity output_quantity
       FROM (SELECT productname,
                    sum(qty) quantity
                    FROM input
                    GROUP BY productname) input
            LEFT JOIN (SELECT productname,
                              sum(qty) quantity
                              FROM output
                              GROUP BY productname) output
                      ON output.productname = input.productname;

Answer 3

I think union all is the way to go. I would do:

select productName, sum(input) as input_qty, sum(output) as output_qty
from ((select productName, qty as input, null as output
       from input
      ) union all
      (select productName, null, qty
       from output
      )
     ) io
group by productName;

Note that productName only appears once in the result set. I see no reason to include it twice. If there is no output (or no input) row, then the corresponding qty will be NULL.

Answer 4

Welcome to SO

There is a way without subquery to achieve this. It's mathematical :)

Since you go trought 2 tables, you will get the result multiply by IDs, by Product. So just divide the sum by the the count of product name divide by unique IDs corresponding to those products :)

So something like this will do the trick : Schema (MySQL v5.7)

CREATE TABLE INPUT (
  `ID` INTEGER,
  `productName` VARCHAR(9),
  `QTY` INTEGER,
  `buyPrice` INTEGER,
  `sellPrice` INTEGER
);

INSERT INTO INPUT
  (`ID`, `productName`, `QTY`, `buyPrice`, `sellPrice`)
VALUES
  ('1', 'dress 007', '2', '700', '1400'),
  ('2', 'shirt 001', '4', '800', '1900'),
  ('3', 'dress 007', '10', '700', '1500'),
  ('4', 'dress 007', '6', '900', '2900'),
  ('5', 'shirt 001', '2', '750', '1600'),
  ('6', 'hat 008', '2', '300', '600');

CREATE TABLE OUTPUT (
  `ID` INTEGER,
  `productName` VARCHAR(9),
  `QTY` INTEGER
);

INSERT INTO OUTPUT
  (`ID`, `productName`, `QTY`)
VALUES
  ('1', 'dress 007', '4'),
  ('2', 'shirt 001', '2'),
  ('3', 'dress 007', '1'),
  ('4', 'dress 007', '1'),
  ('5', 'shirt 001', '3');

---

Query #1

SELECT I.productName, 
       SUM(I.QTY)/(COUNT(I.productName)/count(distinct I.ID)) 
                                          as "SUM(QTY)INPUT",
       O.productName,                      
       SUM(O.QTY)/(COUNT(O.productName)/count(distinct O.ID))  
                                          as "SUM(QTY)OUTPUT"
FROM INPUT I
LEFT OUTER JOIN OUTPUT O ON I.productName = O.productName
GROUP BY I.productName
ORDER BY 2 DESC;

| productName | SUM(QTY)INPUT | SUM(QTY)OUTPUT | productName |
| ----------- | ------------- | -------------- | ----------- |
| dress 007   | 18            | 6              | dress 007   |
| shirt 001   | 6             | 5              | shirt 001   |
| hat 008     | 2             | null           | null        |

---

https://www.db-fiddle.com/f/Dp7yaNkVf3JW2DZrrvL7G/1