Posted 10 months 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)
Once an application no longer requires constant development, it needs periodic maintenance for stable and secure operation. makandra offers monthly maintenance contracts that let you focus on your business while we make sure the lights stay on.
