Thread

Re: [SQL] Oddities with NULL and GROUP BY

secret <secret@kearneydev.com> — 1999-05-17T13:49:54Z

"Jackson, DeJuan" wrote:

> The behavior is valid, if you define NULL as meaning undefined.
> In other words when you define something as NULL you're saying, "I don't
> know what it is. It could be equal or not."
>         -DEJ
>
> > -----Original Message-----
> > From: secret [SMTP:secret@kearneydev.com]
> > Sent: Friday, May 14, 1999 11:58 AM
> > To:   PG-SQL
> > Subject:      [SQL] Oddities with NULL and GROUP BY
> >
> >     Maybe there is something I don't know about how GROUP BY should
> > work, but if I have a table like:
> > a,b,c
> > 1,1,1
> > 1,1,2
> > 1,1,3
> > 1,2,1
> > 1,3,1
> >
> > And I say SELECT a,b,sum(c) FROm .. GROUP BY a,b I get
> > 1,1,6
> > 1,2,1
> > 1,3,1
> >
> > So whenever a or b changes we get a new summed row, well if I have rows
> > where a or b are null, this doesn't happen, infact I seem to get all
> > those rows individually... Like if:
> > 1,1,1
> > 1,1,3
> > 1,NULL,10
> > 1,NULL,20
> > 1,2,3
> >
> > I get:
> > 1,1,4
> > 1,NULL,10
> > 1,NULL,20
> > 1,2,3
> >
> > Shouldn't I get 1,NULL,30?  Ie shouldn't NULL be treated like any other
> > value?  Or is there some bit of information I'm missing?  I can set
> > everything from NULL to 0 if need be, but I'd rather not...
> >
> > David Secret
> > MIS Director
> > Kearney Development Co., Inc.
> >

    Oh, I just observed this oddity... PostgreSQL groups just fine when there
is a table of 2 fields a int4, b int4...

SELECT a,sum(b) FROM z GROUP BY a         Groups NULLs fine
SELECT a,b,sum(c) FROM z GROUP BY a,b    Error in grouping NULLs in b...