no count of zero

Count doesn't seem to be returning zero when there are no records. I've
checked my joins. I've tried ISNULL(Count(*), 0) [which I've NEVER had to do
before] to handle a null count (still no 0). It's a new machine to me, and
I'm wondering if there is some system setting I need to make sure count
returns a zero when no records are returned.
I can switch to returning @.@.RowCount value, but that's not what I'm trying
to do.
Any help?
Thanks
--
Buz WaitzPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
You might want to look up the behavior of an empty table and aggregate
functions; COUNT(*) and COUNT(<exp> ) are not the same.|||Buz (bwaitz@.minormiracles.com.nospamplease) writes:
> Count doesn't seem to be returning zero when there are no records. I've
> checked my joins. I've tried ISNULL(Count(*), 0) [which I've NEVER had
> to do before] to handle a null count (still no 0). It's a new machine to
> me, and I'm wondering if there is some system setting I need to make
> sure count returns a zero when no records are returned.
Better if you post the code you are having problem with. Else we are
completely in the dark of trying to understand what you are trying to do.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Found it. It was a group by clause that caused the problem.
Thanks,
--
Buz Waitz
"--CELKO--" wrote:

> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> You might want to look up the behavior of an empty table and aggregate
> functions; COUNT(*) and COUNT(<exp> ) are not the same.
>

Next IsNull help required

Hi,

I have written a simple formula to identify the last address in a sequence, in a multiple address report which is sorted in alphabetical order:

If next ({address}) <> {address} then 1 else 0

This works fine, however, the very last record in the report 'should' have a value of 1 but doesn't because obviously there is no Next record.

I have therefore tried adding the following line (and variations on the same theme!) but cannot achieve what I want.

If Next ({address}) <> {address} then 1 else
If Next IsNull ({address}) then 1 else 0

I presume this is not possible as there is no Next record. Can anyone suggest a way around this?

Thanks.OnLastRecord|||Thanks. My report now works. Apologies for being a numbskull!