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 ";
"

what do you mean "it does not work"? did you get an error? did you get bad results? detail please — No Idea For Name
– No Idea For Name, Commented Nov 6, 2014 at 9:40
I am sorry it did not show the expected result. @no idea for name — xyonme
– xyonme, Commented Nov 6, 2014 at 9:41
table1 and table2 has same attribute : user_id ;table1.table1_user_id = table2.table2_user_id =user.user_id — xyonme
– xyonme, Commented Nov 6, 2014 at 9:46
well, check the inner select. if it's ok then the join doesn't return any couples and the LEFT word make it so that you only get the rows from table1. i don't think it's good comparing dates unless you are absolotly sure they will be the same — No Idea For Name
– No Idea For Name, Commented Nov 6, 2014 at 9:52

David162795 · Accepted Answer · 2014-11-06 10:19:47Z

1

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

edited Nov 6, 2014 at 10:19

answered Nov 6, 2014 at 9:52

David162795

1,8763 gold badges15 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

xyonme Over a year ago

HI David. Thnak you for your answer but you should know table1_id and table2_id is not the same and they might not have the same number of rows. I have applied your SQL but I got this error "#1222 - The used SELECT statements have a different number of columns"

David162795 Over a year ago

That doesn't matter for union and are you sure you copied the query completely? That error means you had to miss a column in one of these two subquery selects.

David162795 Over a year ago

Actually table id is not even needed, as it is not used, just user id. I removed it from my answer.

xyonme Over a year ago

I'm sorry I missed the 0 as a1 in the second UNION SQL. However, The result is 0 for each table2_amount row. FYI, number of rows for table2 : 226 rows when it is summed and group by. For table1 there is 569 rows. when I use your SQL, it just shows 569 rows. it didnot combine the rows. it seems like table1 left join table2.

David162795 Over a year ago

Can you show me how you edited that query for your actual needs? You may still have a bug in it somewhere. (use pastebin pls)

|

xyonme · Accepted Answer · 2014-11-07 04:57:39Z

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"

Collectives™ on Stack Overflow

MYSQL sum from two different tables group by

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related