Posted 5 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:

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)
Growing Rails Applications in Practice
Check out our new e-book:
Learn to structure large Ruby on Rails codebases with the tools you already know and love.

Owner of this card:

Avatar
Natalie Krehan
Last edit:
5 months 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 Krehan to makandra dev
This website uses cookies to improve usability and analyze traffic.
Accept or learn more