[postgis-users] Postigs layer in Mapserver

[Jan Hartmann]

You are right. The example should run as shown, but it can be made 
slightly less complex:

select the_geom from
zipcodes,
(select postal_code, count(*) as total_pop
      from data
      group by postal_code
) as aggrdata
where zipcodes.postal_code = aggrdata.postal_code

and the MapServer labelitem can be just:

LABELITEM postal_code

Jan

Paul Ramsey wrote:
> You also made a small error in your example of what cannot be included 
> in a "GROUP BY" situation. Citing the example
> 
> SELECT COUNT(*) FROM THETABLE GROUP BY POSTAL_CODE
> 
> you said that one could not just include other attributes in the SQL, 
> such as POSTAL_CODE, for example.  However, POSTAL_CODE is the one 
> attribute you *can* include in this situation, since it is the grouping 
> variable.
> 
> SELECT POSTAL_CODE,COUNT(*) from THETABLE GROUP BY POSTAL_CODE
> 
> will return the count of postal codes for each distinct postal code. As 
> you noted, for any *other* variable, the only way to include them is to 
> use an aggregate function, like min() or max().
> 
> Note that you can also include geometries in these statements, but only 
> if you use an spatial aggregate function. Currently, there are only two 
> spatial aggregates in PostGIS: extent(), and collect(). Extent() returns 
> the maximum bounding box of the set of geometries, and collect() returns 
> a geometrycollection of the set.  Once we have spatial operators like 
> union(<geometry>,<geometry>) it might make sense to also create an 
> aggregate function union() (although it would be incredibly slow I am 
> sure).
> 
> P.
> 
> Jan Hartmann wrote:
> 
>> Sorry, typo again (forgot to install my SQL-checker). The final select 
>> should be:
> 
>