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

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
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>

And count just adds the number to the running tally.

Mike Pollard
SUPRA Server SQL Engineering and Support
Cincom Systems, Inc.
--------------------------------
Better to remain silent and be thought a fool than to speak out and
remove all doubt.
Abraham Lincoln