Oracle returns 0 when there is no data in SUM and NVL

Oracle returns 0 when there is no data in SUM and NVL

Use SUM and NVL in Oracle to return 0 if there is no data.

Suppose you have the following fee table.

There is a NULL in the RYOKIN column, but the SQL can be issued as follows to obtain the correct total.

SELECT SUM(RYOKIN)
FROM RYOKINTABLE

Therefore, it is not necessary to use SUM(NVL(RYOKIN),0).

However, the following condition will result in 0 data.

SELECT SUM(RYOKIN)
FROM RYOKINTABLE
WHERE ID = 3

The result of this SQL will be NULL. The result of SUM will be NULL if there is no data matching the condition.

So, if you want to consider that the condition is not met (data cannot be retrieved) and return 0 in case of NULL, enclose the SUM in NVL as follows

SELECT NVL(SUM(RYOKIN),0)
FROM 料金テーブル
WHERE ID = 3

This will return 0 for the result.

高橋