What is the difference between nvl(sum (field), 0) & sum(nvl (field,0)) ?
760519Apr 1 2010 — edited Apr 1 2010What is the difference between nvl(sum (field), 0) & sum(nvl (field,0)) ?
For the below table data i don't see any different in results
CREATE TABLE FRUITS
(
TYPE VARCHAR2(10 BYTE),
VARIETY VARCHAR2(10 BYTE),
PRICE NUMBER(18,2)
)
TYPE VARIETY PRICE
apple gala 2.79
apple fuji 0.24
apple limbertwig 2.87
orange valencia 3.59
orange navel 9.36
pear bradford 7.77
pear bartlett 7.77
cherry bing 2.55
cherry chelan 6.33
pear bradford
navel 6.39
select variety, nvl(sum(price),0)
from fruits
group by variety;
VARIETY NVL(SUM(PRICE),0)
limbertwig 2.87
bartlett 7.77
bing 2.55
marathon 0
gala 2.79
fuji 0.24
navel 15.75
bradford 7.77
chelan 6.33
valencia 3.59
select variety, sum(nvl(price,0))
from fruits
group by variety;
VARIETY SUM(NVL(PRICE,0))
limbertwig 2.87
bartlett 7.77
bing 2.55
marathon 0
gala 2.79
fuji 0.24
navel 15.75
bradford 7.77
chelan 6.33
valencia 3.59
no difference in output.
what is the difference?
Thanks in advance