Posted over 3 years 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:

Copy
mysql> 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:

Copy
test_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 SUM if it's an option to sum up a column of a result set instead of expressions (SUM ignores NULL values)
  • wrap columns with: COALESCE(column, 0)(COALESCE takes the first non-null argument)

Your development team has a full backlog of feature requests, chores and refactoring coupled with deadlines? We are familiar with that. With our "DevOps as a Service" offering, we support developer teams with infrastructure and operations expertise.

Owner of this card:

Avatar
Natalie Zeumann
Last edit:
over 3 years ago
by Arne Hartherz
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Natalie Zeumann to makandra dev
This website uses short-lived cookies to improve usability.
Accept or learn more