Posted about 1 year ago. Visible to the public. Repeats.
Don't sum up columns with + in a SQL query if NULL-values can be present.
Don't sum up columns with + in a sql-query if NULL-Values can be present.
MySQL and PostgreSQL cannot sum up NULL values with the + value. The sum value will be NULL.
MySQL:
Copymysql> select 1 + 2 + 3; +-----------+ | 1 + 2 + 3 | +-----------+ | 6 | +-----------+ 1 row in set (0,00 sec) mysql> select 1 + NULL + 3; +--------------+ | 1 + NULL + 3 | +--------------+ | NULL | +--------------+ 1 row in set (0,00 sec)
Postgres:
Copytest_database=# select 1 + 2 + 3; ?column? ---------- 6 (1 row) test_database=# select 1 + NULL + 3; ?column? ---------- (1 row)
If you want to do additions in the database:
- use
SUMif it's an option to sum up a column of a result set instead of expressions (SUMignoresNULLvalues) - wrap columns with:
COALESCE(column, 0)(COALESCEtakes the first non-null argument)
Check out our new e-book:
Learn to structure large Ruby on Rails codebases with the tools you already know and love.
