mysql - How to sum amounts according their ID-s and add the results to a new column on an existing table -

mysql - How to sum amounts according their ID-s and add the results to a new column on an existing table -

i have database table (the table below) grow fast has more 4 1000000 rows

this table taking about:

# name type collation attributes null default action 1 user_id int(11) no none alter change drop drop browse distinct values browse distinct values primary primary unique unique show more actions more 2 amount decimal(16,8) no none alter change drop drop browse distinct values browse distinct values primary primary unique unique show more actions more 3 aff int(11) no 0 alter change drop drop browse distinct values browse distinct values primary primary unique unique show more actions more 4 jackpot int(11) no 0 alter change drop drop browse distinct values browse distinct values primary primary unique unique show more actions more 5 paidout int(11) no 0 alter change drop drop browse distinct values browse distinct values primary primary unique unique show more actions more 6 type int(11) no 0 alter change drop drop browse distinct values browse distinct values primary primary unique unique show more actions more 7 created timestamp no current_timestamp alter change drop drop browse distinct values browse distinct values primary primary unique unique show more actions more

i run query sum amounts user_id , results

select user_id, sum(amount) trans grouping user_id;

my question how can add together these results (sum-s)to existing table have same user ids , save these sum-s there

the existing table have has structure

# name type collation attributes null default action 1 id int(4) no none auto_increment alter change drop drop browse distinct values browse distinct values primary primary unique unique show more actions more 2 balance int(11) no none alter change drop drop browse distinct values browse distinct values primary primary unique unique show more actions more 3 ads varchar(60) latin1_swedish_ci no none alter change drop drop browse distinct values browse distinct values primary primary unique unique show more actions more 4 ip varchar(85) latin1_swedish_ci no alter change drop drop browse distinct values browse distinct values primary primary unique unique show more actions more 5 hash varchar(150) latin1_swedish_ci no none alter change drop drop browse distinct values browse distinct values primary primary unique unique show more actions more 6 email varchar(150) latin1_swedish_ci no none alter change drop drop browse distinct values browse distinct values primary primary unique unique show more actions more 7 aff int(11) no 0 alter change drop drop browse distinct values browse distinct values primary primary unique unique show more actions more 8 int(11) no 0 alter change drop drop browse distinct values browse distinct values primary primary unique unique show more actions more 9 timestamp bigint(20) unsigned no 0 alter change drop drop browse distinct values browse distinct values primary primary unique unique show more actions more

i prefer place sums on new column after id column.

you can following:

alter table table2 add together column thesum decimal(16, 8); update table2 t2 set thesum = (select sum(amount) table1 t1 t2.user_id = t1.user_id)

now, there issues this. when values alter in first table, not alter in sec table. create happen, need utilize trigger re-adjust values.

often, recommended utilize views such process. however, not particularly efficient. guess want summarize values performance reasons. if so, careful keeping values consistent.

edit:

to add together in sum, do:

update table2 t2 set thesum = thesum + coalesce((select sum(amount) table1 t1 t2.user_id = t1.user_id), 0);

mysql sql