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?

On Nov 1, 1:55 pm, trp... (AT) gmail (DOT) com wrote:

SELECT SUM(nvl(cases_escalated, 0)) AS casesescalated
FROM groupsummary
WHERE report_date=TO_DATE('Sep 2007','MON YYYY')
AND group_name = 'T1';



David Fitzjarrell

trpost (AT) gmail (DOT) com wrote:
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...

fitzjarrell (AT) cox (DOT) net schrieb:
All aggregate functions (except count(*) and grouping)
http://download.oracle.com/docs/cd/B...001.htm#i89203
ignore nulls, so there is no point to sum 0 instead of null, because the
result is the same.
To display 0 in place of NULL one could do

SELECT nvl(SUM(cases_escalated),0) AS casesescalated
FROM groupsummary
WHERE report_date=TO_DATE('Sep 2007','MON YYYY')
AND group_name = 'T1'

however, i would still prefer NULL to be distinguished from 0

Best regards

Maxim

On Nov 1, 3:09 pm, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) com>
wrote:
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