CODEWITHSUNDEEP
mysqlsql
Ted
Ted

Reputation: 4166

MySQL: sum cells in column and insert it in same table with condition

I need to sum specific cells in column with condition and insert it in same table

table structure:

id    user_id      type       scores
1        1         daily        10
2        1         daily        20
3        1          all         500
4        1         daily         5
5        2          all          200
6        3          all          300
7        2         daily         23
8        1          cat          11
9        2         daily         25
10       3         daily         30

each user have only one "all" score (in "type" column ) and multiple "daily" scores,

how to sum "daily" scores and insert it in "all" row

like:

id    user_id      type       scores
1        1         daily         10
2        1         daily         20
3        1          all          35
4        1         daily         5
5        2          all          48
6        3          all          30
7        2         daily         23
8        1          cat          11
9        2         daily         25
10       3         daily         30

thanks,

Upvotes: 0

Views: 709

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270553

You are doing an update, not an insert. You can use join:

update structure s join
       (select user_id, sum(scores) as sums
        from structure s
        where type = 'daily'
        group by user_id
       ) ss
       on s.user_id = ss.user_id
    set s.scores = ss.sums
    where s.type = 'all';

Upvotes: 2

Related Questions