Count() versus Sum() in Cognos

[iren]

Hi everybody,

I ran query in Cognos Imprompru in order to get how many members account has.

When I built my query first I used COUNT function and got 18,000. While having used SUM function I got 20,000 members.

I wonder what caused this difference. I would expect in such a case the result shou;d be the same.

Could you please explain me how these funclions differ?

Thank you in advance,
Iren

 

[blom0344]

That is very basic. You use a count on non-numeric data, like counting the number of customers. There are 2 type of counts, count and distinct count.
The latter one removes duplicates from the result.
Sum is used on facts/measures, like getting the overall revenue for all customers.

I am not familiar with impromptu, but there is a good possibility, that 18000 is the distinct count and 20000 is somehow produced as a normal count (although sum is defined)

Ties Blom

 

[iren]

Ties,
Thank you for your response. The thing is that Filter(that I applied for the query) had "Elminated duplicates" option which I chosed. So my understanding was that I used distinct Count.

However....does it count nulls? If yes, then probably it would explain the difference.....

 

[blom0344]

Nope, nulls are excluded from a count operation.

Ties Blom

 

[iren]

....and does it count zeroes?

 

[blom0344]

Yes. (but if you remove duplicates and perform a distinct count, than every distinct values is only counted once!)

Ties Blom