How can I merge Monthly Sales and Monthly Purchase table into one singe table?

user_16254829 0
Viewed 46

I have two table: Monthly Sales(month, total_sales) and Monthly Purchase(month, total_purchase). I need to combine both the table and output (month, total_sales, total_purchase).

Month_Sales:       Monthly_Purchase:
+----+----------+  +-----+-------------+
| Month | sales |  |  Month | purchase |
+----+----------+  +-----+-------------+
| Jan  | 50000  |  | Jan    | 50000    |
| Mar  | 20000  |  | Feb    | 60000    |
| Jun  | 10000  |  | Mar    | 40000    |
+----+----------+  +-----+-------------+

Output:

+----+----------+---------+
| Month | sales | purchase|
+----+----------+---------+
| Jan  | 50000  | 50000   |
| Feb  |  NULL  | 60000   |
| Mar  | 20000  | 40000   |
| Jun  | 10000  | NULL    |
+----+----------+---------+

I try to achieve this using FULL OUTER JOIN but it does not provide expected.

SELECT Table1.month, Table1.sales, Table2.purchase FROM (SELECT month, sales from Monthly_Sales) as Table1
FULL OUTER JOIN (SELECT month, purchase from Monthly_Purchase) as Table2
ON Table1.month = Table2.month;

So what should I do?

outer-join merge join mysql sql
1 Answers

You can use union all and group by:

select month, sum(sales), sum(purchase)
from ((select month, sales, null as purchase
       from sales
      ) union all
      (select month, null, purchase
       from purchases
      )
     ) sp
group by month;
user_11440350
Related