mysql - Query to sum two tables -

mysql - Query to sum two tables -

query sum 2 tables

i have transaction table , payment table. need total amount both on trans table , payment table , must grouping transid. hope can help me right sql statement this.

i tried sql below sum of amount on trans table multiplied number of records of same transid on payment table.

-> select trans.transid, sum(trans.amount), sum(payment.amount) trans left bring together payment on payment.transid = trans.transid

i tried sql below processing time longer compared sql statement above.

-> select td.transid, td.amt, pd.paid ( select trans.transid, sum(trans.amount) amt trans grouping trans.transid ) td left bring together ( select payment.transid, sum(payment.amount) paid payment grouping payment.transid ) pd on pd.transid = td.transid

trans table

transid | amount t1 | 10 t2 | 15 t3 | 12 t4 | 20 t5 | 11 t1 | 15 t5 | 14 ----------

payment table

transid | amount t1 | 5 t3 | 10 t1 | 3 t2 | 5 t5 | 10 ----------

the query result need supposed way:

transid | amount | paid t1 | 25 | 8 t2 | 15 | 5 t3 | 12 | 10 t4 | 20 | 0 t5 | 25 | 10

you can utilize 2 different queries, 1 sums amounts (and sets paid column 0) , 1 sums paid values (and sets amount column 0) , combine using union query, , sum results:

select transid, sum(amount) amount, sum(paid) paid ( select transid, sum(amount) amount, 0 paid trans grouping transid union select transid, 0 amount, sum(amount) paid payment grouping transid ) s grouping transid

please see fiddle here.

mysql sql table join sum