Subject | Re: [firebird-support] Looking for explanation of count and group by |
---|---|

Author | Bob Murdoch |

Post date | 2003-09-12T15:49:17Z |

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

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

>Bob Murdoch wrote:No, just convoluted logic brought about by trying to produce a result set

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

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