Chris Date says: "... the aggregate functions (SUM, AVG, etc) ... simply egnore any nulls in their argument (except for COUNT(*)) ... if the argument to such a function happens to evaluate to an empty set, the functions all return null (except for COUNT, which returns zero."
This from page 222-3,_A Guide to the SQL Standard_, Third Edition, Date C.J. and Hugh Darwen, 1993 Addison Wesley. Paragon Corporation wrote: > Brent > > >> That gets back to how nulls should be handled generally. >> > > >> If you take the sum() (or avg(), max(), etc) of a numeric column with >> > nulls, what should the result be? > > >> IMO it should be a null, as we really don't know. If the nulls in the set >> > comprise unknown values, then any result predicated on them is also unknown. > > > >> If the user wants the sum(), etc, of the not null values then they can ask >> > for that easily enough via a where clause. > > All RDBMS I know when you sum up things with nulls you get the result > without the null (there are rare aggregates where it is not advantageous to > do that). Whether that is right or wrong is another question, but it seems > pretty baked into the standard > of how RDBMS work and its convenient because in general that's the behavior > you want and its easy to generate the reverse behavior of what you describe > by slapping a coalesce/case when everywhere, but its not quite so easy to go > the other way around. Think about it how would you say count only things > that aren't null. > > > >> I'm also unclear on the way Postgis treats empty geometries vs null >> > geometries, or if they are functionally the same thing. > > >> Is an empty geometry value different from a null in a geometry column? >> Can an empty geometry have a SRID (& a null can't)? >> In what cases would it make sense to replace null geometries with empty >> > ones? > > No. They are not functionally the same. For example the intersection of 2 > disjoint geometries is obviously an empty collection - it is known therefore > it is not null > And that empty collection should have the same srid of the 2 geometries > being intersected. > > Thanks, > Regina > > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > -- Regards, Chris Hermansen mailto:[EMAIL PROTECTED] tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.232.0644 Timberline Natural Resource Group · http://www.timberline.ca 401 · 958 West 8th Avenue · Vancouver BC · Canada · V5Z 1E5 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
