MYSQL sum from two different tables group by

Question

I have two tables. I need to combine each row of these two tables into a row in table3. I managed to get the table1 SUM amount but not table2.

Eg.

table user

 +---------+-----------+
 | user_id | user_name |
 +---------+-----------+
 | 001     |   JOHN    |
 | 002     |   ADAM    |
 +---------+-----------+

table1

+-----------+----------------+-------------------+---------------------+
| table1_id | table1_user_id |    table1_amount  |       table1_date   |
+-----------+----------------+-------------------+---------------------+
|  6        | 001            |    100            |  01/11/2014 10:55   |  
|  7        | 002            |   100             |  01/11/2014 10:55   | 
|  8        | 001            |    50             |  25/10/2014 10:55   |  
|  9        | 001            |   100             |  23/10/2014 11:00   |  
|  10       | 002            |    0              |  21/10/2014 11:00   | 
+-----------+----------------+-------------------+---------------------+ 

table2

 +-----------+----------------+----------------+--------------------+
 | table2_id | table2_user_id |  table2_amount |  table2_date       |   
 +-----------+----------------+----------------+--------------------+
 |   1       |    001         |   100          |   15/11/2014 10:55 |    
 |   2       |    001         |   100          |   15/10/2014 10:55 |     
 |   3       |    002         |   100          |   11/10/2014 10:55 |     
 |   4       |    001         |   50           |   11/10/2014 10:55 |    
 +-----------+----------------+----------------+--------------------+

Expected Result:

Table3

+-----+---------+---------------+---------------+----------+---------+
| id  | user_id | table1_amount | table2_amount |    Year  |  Month  |
+-----+---------+---------------+---------------+----------+---------+
| 1   |  001    |    100        |       100     |     2014 |    11   |
| 2   |  002    |    100        |        0      |     2014 |    11   |
| 3   |  001    |    150        |       150     |     2014 |    10   |
| 4   |  002    |     0         |       100     |     2014 |    10   |
+-----+---------+---------------+---------------+----------+---------+

My try but it does not show the expected result. The amount of table2_amount in every row is NULL :

SQL=" INSERT INTO table3 
      SELECT user_id,SUM(table1_amount),t2.amount2,
      YEAR(table1_date),MONTH(table1_date) FROM table1 a 
      LEFT JOIN 
     (SELECT c.table2_user_id,SUM(c.table2_amount) as amount2,c.table2_date
              FROM   table2 c
       GROUP BY DATE_FORMAT(c.table2_date,'%Y-%m'),c.table2_user_id ASC
              ) t2
     on t2.table2_user_id = a.table1_user_id AND t2.table2_date = a.table1_date
     GROUP BY DATE_FORMAT(a.table1_date,'%Y-%m'),table1_user_id ASC ";
"

Answer 1

Thanks to David162795 for the enlightening discussion.

The missed point is group INNER QUERY by date and user id when the date from two tables are different. We need to group them by their individual date in the Inner Query and then group the main SELECT query by the time variable.

This goes my answer for this case :

$SQL = "
 INSERT INTO table3 (user_id, table1_amount, table2_amount,Year,  Month)
 SELECT tx.uid, SUM(tx.sum1), SUM(tx.sum2),YEAR(tx.d) as year,MONTH(tx.d) as month 
  FROM 
  (SELECT  b.table1_user_id as uid,b.table1_amount as sum1,0 as sum2,
           b.table1_date as d  FROM   table1 b
    GROUP BY DATE_FORMAT(d,'%Y-%m'),uid ASC

    UNION

    SELECT c.table2_user_id as uid,0 as sum1,
           sum(c.table2_amount) as sum2,c.table2_date   as d1
              FROM   table2 c
    GROUP BY DATE_FORMAT(d1,'%Y-%m'),uid ASC
  ) tx 

 GROUP BY year,month,uid"

Answer 2

This a nice task for UNION

SELECT tx.uid,SUM(tx.a1),SUM(tx.a2),YEAR(tx.d),MONTH(tx.d)
FROM
(
SELECT t1.table1_user_id as uid,
  t1.table1_amount as a1,
  0 as a2,
  t1.table1_date as d 
  FROM table1 t1
UNION
SELECT t2.table2_user_id as uid,
  0 as a1,
  t2.table2_amount as a2,
  t2.table2_date as d 
  FROM table2 t2
) tx
GROUP BY DATE_FORMAT(d,'%Y-%m'),uid ASC