Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: oracle sum display zero instead of NULL
On Nov 1, 3:09 pm, Frank van Bortel <frank.van.bor..._at_gmail.com>
wrote:
> trp..._at_gmail.com wrote:
> > I have a simple query:
>
> > SELECT SUM(cases_escalated) AS casesescalated FROM groupsummary WHERE
> > report_date=TO_DATE('Sep 2007','MON YYYY') AND group_name = 'T1';
>
> > The query works fine, except for when there is nothing to SUM and then
> > I get returned NULL instead of 0.
>
> > My question is how can I get the query to display 0 instead of
> > 'blank'; I read something about using a join, but I don't see how
> > joining would help me as right now this is the only table in my
> > database. Would I somehow join against a query to DUAL?
>
> By not summing NULLs: sum(nvl(cases_escalated,0)),
> or by decoding the NULL: nvl(sum(cases_escalated),0)
>
> Which ever way you want it
> --
> Regards,
> Frank van Bortel
>
> Top-posting is one way to shut me up...
Use nvl(sum... rather than sum(nvl...
sum(nvl... will still return null if there are no rows that satisfy
the "where" clause.
SQL> select sum(nvl(null,0)) from dual where 0=1;
SUM(NVL(NULL,0))
SQL> select nvl(sum(null),0) from dual where 0=1;
NVL(SUM(NULL),0)
0 Received on Fri Nov 02 2007 - 09:38:52 CDT
![]() |
![]() |