Search code examples
mysqlsqlsumcalculated-columns

MYSQL Update using SUM the same table

Hi so sorry for I know this just to basic. simple update only using sum on the same table. I need to get

total_tbl 
+-- month1 --- month2 --- month3 --- total --+ 
|     3          3          5                |
|     5          3          5                |
|     3                     4                |
|                5          5                |
+--------------------------------------------+

I need update the total column using SUM.

I have this statement so far:

UPDATE total_tbl SET total = (SELECT SUM(month1,month2,month3))

I should update even if one column doesn't have a value. Thanks!

Solution

  • SUM() is used to sum an expression across multiple rows, usually using GROUP BY. If you want to add expressions in the same row you just use ordinary addition.

    Use COALESCE() to provide a default value for null columns.

    UPDATE total_tbl
SET total = COALESCE(month1, 0) + COALESCE(month2, 0) + COALESCE(month3, 0)