mysql - How to sum(amount) and group by user_id on the same table -

mysql - How to sum(amount) and group by user_id on the same table -

i have table lot of rows per user_id

and trying grouping rows user_id , sum amount

this table structure

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 trying query without success:

update trans select * trans grouping user_id set amount = (select sum(amount) trans

any help appreciated

you can :

update trans t inner bring together ( select user_id, sum(amount) sumamount trans grouping user_id ) subsum on subsum.user_id = t.user_id set t.amount = subsum.sumamount with user_id range : update trans t inner bring together ( select user_id, sum(amount) sumamount trans user_id between 0 , 1000 --edited grouping user_id ) subsum on subsum.user_id = t.user_id set t.amount = subsum.sumamount t.user_id between 0 , 1000 --here with temp table

using temp table :

--create table user_id , sum amount create table trans_temp_sum_amount select user_id, sum(amount) sumamount trans grouping user_id; --update update trans t inner bring together trans_temp_sum_amount subsum on subsum.user_id = t.user_id set t.amount = subsum.sumamount; --drop temp table drop table trans_temp_sum_amount;

mysql sql