mysql count total of muliple columns

Question

hi i have a table as follow:

user table

id  val1 val2 val3
1    2    3    2
2    0    0    1

i would like to know how to count value from columns val1,val2,val3 where id = 1 ? which means for id = 1 total would be 7 and for id = 2 total would be 1. Most of SO example count the entire columns for all ids.My solution as follow seems not get preferred result

select count(*) as tot
from (
select val1 as total from user
where id=1
union
select val2 as total from user
where id=1
union
select val3 as total from user
where id=1
) as x 

Thanks for help.

Answer 1

You can simply add the columns together:

SELECT val1 + val2 + val3 FROM user WHERE id = 1

See it on sqlfiddle.

---

UPDATE

To exclude NULL values, use MySQL's IFNULL() function:

SELECT IFNULL(val1,0) + IFNULL(val2,0) + IFNULL(val3,0) FROM user WHERE id = 1

See it on sqlfiddle.

Answer 2

It looks like you need to sum the column values for each user id. This will do:

select id, coalesce(val1, 0) + coalesce(val2, 0) + coalesce(val3, 0) 
from user

Notice that coalesce is a SQL ANSI defined function, thus, it will work on other DBMSs as well. You can use IFNULL(), but that will only work in MySQL.