Count returning 0 - but how does it?

building reports desktopintelligence
1

Not sure if this is to be in Reporter or Designer…but I’ll start here.

I have a couple counts that were created by consultants for me. Here is one of them:

count(distinct CASE WHEN A = 1 AND B = 1 AND
C = 1 AND D = 1 THEN XYZ ELSE NULL END)

If I run this over a time frame, and there are no cases where A,B,C,D are all true, this will give me a count of ‘zero’ sometimes. In my opinion it wouldn’t be possible if one or all of A,B,C,D are untrue. In that case it would be null, correct? Does anyone have any ideas?

I’m stuck!

Chad (BOB member since 2003-04-21)

2

This is happening because, Count function always returns a number (0 or any other number ), it counts null and non null values

Reema

reemagupta (BOB member since 2002-09-18)

3

Oracle will return count(NULL) = 0.

You may want to modify your formula as below


CASE WHEN A=1 AND B=1 AND C=1 AND D=1
THEN count(distinct XYZ)
ELSE NULL
END

Anjan Roy (BOB member since 2002-07-10)

4

Is this true? If I have no records during a time frame it doesn’t give me a number of 0 or any other number. It simply does not display anything.

Is it maybe true if there are records during the time frame but do not meet the criteria…then it returns a number of 0?

Chad (BOB member since 2003-04-21)

5

If there are ROWS for that period, you will get a count of 0 or a valid count. If there are no ROWS, then you will get a NULL value. There has to be something to count (even the DUAL/DUMMY table contains a single row so that values will be returned).

Empty tables will return null counts (or no rows/no data to fetch). Keep in mind that a COUNT of any Oracle field is (by default) a count of NON-NULL rows. This differs slightly from the Count(*) or Count(1) that is typically used to return a count of rows.

-RM

digpen :us: (BOB member since 2002-08-15)

6

Is there anyway to remove this from showing in the output? I know I can do a

=if xyz <> 0 then xyz

to not display it. But then I have an empty row of data. This gets very confusing for the user. Sometimes a count of zero is displayed for a date (or the date is shown with an empty…null…value), other times the date is simply not displayed as there is no data to fetch for that day.

Chad (BOB member since 2003-04-21)