nenad007 nenad007 - 11 months ago 47
MySQL Question

update value with sum of multiple column

I have a table power, where I wan got the SUM of all my is_* fields and store it in the cnt column.

id | is_yellow | is_green | is_blue | cnt
1 | 1 | 1 | null | -> 2
2 | null | 1 | null | -> 1
3 | 1 | 1 | 1 | -> 3


The query give "Invalid use of group function" back on Update:

UPDATE
power
SET
cnt = (SUM(IFNULL(is_yellow, 0)) +
SUM(IFNULL(is_green, 0)) +
SUM(IFNULL(is_blue, 0)))


What's wrong there?

Answer Source

You can use COALESCE for this:

UPDATE
  power
SET
  cnt = COALESCE(is_yellow, 0) +
        COALESCE(is_green, 0) +
        COALESCE(is_blue, 0) 

If the field is null, then 0 is used in the addition.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download