How to sum mysql vertically?

Question

this is my table

ID angka1 angka2
1  80     20
2  10     20
3  10     20
4         0

I want to sum angka1 vertically, so the value of angka1 on ID 4 is 100, i already try with code below and works, but i want a simple one, because i have another table (this one is just prototype) with many ID, and this code would very long to write.

update halooo set angka1 = (
    select sum(db1+db2+db3) from(
        select
        (select sum(angka1) from halooo where id=1)db1, 
        (select sum(angka1) from halooo where id=2)db2,
        (select sum(angka1) from halooo where id=3)db3
        )total
    )
    where id = 4

i try with this one but this code not update my table,

update halooo 
set 
angka1=(
    select sum(angka1) from(
        select sum(angka1) from halo) a
    )
where id=4

can someone point where is my mistake?

please more spesific ? just use 'SELECT SUM(column_name) FROM table_name;' goes right here http://www.w3schools.com/sql/sql_func_sum.asp — Freddy Sidauruk, Feb 17 '16 at 08:32

eren · Accepted Answer · 2016-02-17T08:55:30.757

2

Try this

UPDATE halooo SET angka1 = (select SUM(angka1) from (SELECT * FROM halooo) as h WHERE id != 4) WHERE id = 4

This should work for your problem if i understood you correctly. It will update column angka1 in row with id 4 with sum of of all rows in column angka1 except row with id 4

edited Feb 17 '16 at 08:55

answered Feb 17 '16 at 08:42

eren

708
8
20

i got an error #1093 - You can't specify target table 'halooo' for update in FROM clause – ridims Feb 17 '16 at 08:48
I update my answer based on http://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause/14302701#14302701 – eren Feb 17 '16 at 08:55

Praveen · Answer 2 · 2016-02-17T08:56:56.727

1

Try;

update halooo h,
(
  select sum(COALESCE(angka1, 0)) sum_val
  from halooo
  WHERE id != 4
) x
set h.angka1 = x.sum_val
where h.id = 4;

demo

edited Feb 17 '16 at 08:56

answered Feb 17 '16 at 08:38

Praveen

8,945
4
31
49

I don't know how mysql handle summing null values, because some DBMS will make the whole sum null, so I would add sum(coalese(angka1,0)) – sagi Feb 17 '16 at 08:41
@Praveen this code works, but the value is increasing, in the first run, the value is 100 (this is what i want), then i run it again and the value become 200, thanks though – ridims Feb 17 '16 at 08:55
@praveen your code is also work, but i can only choose one accepted answer, thanks praveen :) – ridims Feb 17 '16 at 09:02

How to sum mysql vertically?

2 Answers2