Re: oracle sum display zero instead of NULL

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 01 Nov 2007 22:04:18 +0100
Message-ID: <472A3F52.9010903@gmail.com>

fitzjarrell_at_cox.net schrieb:
> On Nov 1, 1:55 pm, 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?

>
>
> 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
>

All aggregate functions (except count(*) and grouping) http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.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 Received on Thu Nov 01 2007 - 16:04:18 CDT