At 9/12/2003 11:31 AM, you wrote:

>Bob Murdoch wrote:
> > Just ran across a situation that I can't explain. Not because anything is
> > wrong, just because I can't figure it out.
> >
> > Given the table "create table test_group_by(column1 integer)"
> >
> > I can run this query and receive a single row result set with a count of 0:
> >
> > select
> > count(*)
> > from
> > test_group_by
>
>Good.
>
> > However, if I change the query to
> >
> > select
> > column1, count(*)
> > from
> > test_group_by
> > group by
> > column1
>
> > I recieve an empty result set. I think I would have expected a single
> row,
> > with column1=NULL and a count of 0.
>
>Why would you expect it? Did you read it in some book, or maybe seen
>some other server does it or ...?

No, just convoluted logic brought about by trying to produce a result set
that contains all possible values, showing 0 where no rows of a value
exist. Something like this:

select
'Approved', count(*)
from
test_group_by
where
column1 = 1

union

select
'Denied', count(*)
from
test_group_by
where
column1 = 0

union

select
'Pending', count(*)
from
test_group_by
where
column1 is NULL


Which produces the correct results. However, if we have another column
like 'Customer_ID' and want to know the results by customer, the above
construct stops working for those states that do not have a row.

I have other ways to tame this beast. As I said, I was just looking for a
definition to apply to what I was observing.

Bob M..