In PostgreSQL, I learned about the convenient COALESCE function that returns an alternative value when a value is NULL, so I’m taking notes.

The SUM function returns NULL when all values in the specified column (in this case, money) are NULL.

Since I wanted the return value to be 0 instead of NULL, I initially wrote it like this:

SELECT
  CASE WHEN SUM(money) IS NOT NULL THEN SUM(money) ELSE 0 END AS total_money
  FROM user;

This type of requirement where you want to return 0 when the SUM function result is NULL can be achieved using the COALESCE function without using a CASE statement.

6.12.2. COALESCE COALESCE(value [, ...]) COALESCE 関数は、NULL でない自身の最初の引数を返します。 データを表示の目的で取り出す際、NULL 値の代わりにデフォルト値を使う場合、時として便利なことがあります。 その例です。 「The COALESCE function returns its first non-NULL argument. It's sometimes useful when retrieving data for display purposes to use a default value instead of NULL values.」

SELECT COALESCE(description, short_description, ‘(none)’) …

・Reference: 条件式

So using the COALESCE function, you can write it like this:

SELECT
  COALESCE(SUM(money), 0) AS total_money
  FROM user;

That’s all from the Gemba regarding PostgreSQL’s COALESCE function.

【References】

・PostgreSQL COALESCEでNULLを空白に変換 - 130単位

・SQL の COALESCE