Re: When SUM is null or zero

Marco Shaw schrieb am 04.08.2005 in
<opoIe.70021$Ph4.2177612_at_ursa-nb00s0.nbnet.nb.ca>:

> **Alert: Oracle & SQL newbie**

> I want to write a report in SQL. Here's a sample table I want to report on:

> server_name severity log_only notification_only
> ------------ -------- -------- ----------------
> serverA critical 0 0
> serverA critical 1 0
> serverA major 1 0
> serverB major 0 0

> The last two columns can only be a zero or one.

> I want a report written in SQL that produces something like:

> server_name no of messages log_only notification_only
> ------------ --------------- --------- ----------------
> serverA 3 2 0
> serverB 1 0 0

> I can easily produce a report with the first 2 columns. Someone indicated
> that completing the report with the last 2 columns could easily be done
> using SUM to count the total value of the applicable columns.

> What I have determined after some testing is that if, for example, the SUM
> of the 4th column from the table adds up to 0, then the final report will
> completely drop the 4th column, and only give me something like
Are you sure that this is an sql- problem? I am afraid that this is a problem of your report- tool.
Show your query and I will try to find out, but first you could try to change cols 3 and 4 in your query and tell us, whether zeros are written then.
you also could try not to show numbers but textes, instead of sum(notification_only) something like sum(notification_only)||' pieces', (but this will change your query into
select
  inner1, ..., inner999||' pieces'
from
  select
    inner1, sum(xxx) as inner2, ... count(yyy) as inner999   from
    TheQueryYouUseNow

> server_name no of messages log_only
> ------------ --------------- ---------
> serverA 3 2
> serverB 1 0

> So how can I take this into account in SQL to make sure the ouput still
> produces a 0 in the 4th column of the report?
May be I understood you totally wrong, but you sure do not have a problem with adding NULL? (in that case look for nvl())

> Thanks,

hth
> Marco

Andreas

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de