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