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.