Re: question about count(b) where b is a custom type

Pollard, Mike schrieb:
> Richard Huxton wrote:
>
>>Pollard, Mike wrote:
>>
>>>>Firstly, if you just want a count, what's wrong with count(1) or
>>>>count(*).
>>>>
>>>
>>>
>>>Because unless the column does not allow nulls, they will not return
>
> the
>
>>>same value.
>>
>>Ah, but in the example given the column was being matched against a
>>value, so nulls were already excluded.
>>
>>--
>
>
> Details, details. But there is a valid general question here, and
> changing the semantics of the query will not address it. When doing a
> count(col), why convert col into a string just so you can determine if
> it is null or not? This isn't a problem on a small amount of data, but

Why convert? A null is always null no matter in which datatype.

> it seems like a waste, especially if you are counting millions of
> records. Is there some way to convert this to have the caller convert
> nulls to zero and non-nulls to 1, and then just pass an int? So
> logically the backend does:
>
> Select count(case <col> when null then 0 else 1) from <table>

Which would be totally silly :-) no matter if its 0 or 1
it counts as 1. Do you mean sum() maybe?
Even then you dont need coalesce to convert null to 0
because sum() just ignores null.

> And count just adds the number to the running tally.

Which number here?

>
> Mike Pollard
> SUPRA Server SQL Engineering and Support
strange...

> Cincom Systems, Inc.