Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

What is the difference between nvl(sum (field), 0) & sum(nvl (field,0)) ?

760519Apr 1 2010 — edited Apr 1 2010
What 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
This post has been answered by Prazy on Apr 1 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 29 2010
Added on Apr 1 2010
2 comments
7,730 views