Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need help with a PostgreSql query. I need to find all parcels which have bigger area then average. I'm using this query:

FROM parcela
WHERE ST_Area(geom)<(SELECT Sum(ST_Area(geom)/count(*)))

but it gives me error:

aggregate function calls cannot be nested

I tried few different ways to solve this (using 'having' instead 'where' etc.) but always some new error occurs. Can anybody please tell me how to write right query to get this information. Thanks

share|improve this question
up vote 3 down vote accepted

One approach, which is a little more basic is to split this into two queries:

SELECT SUM(ST_Area(geom))/count(*) as avg_area
FROM parcela;

Then with this returned value (say it is 500), use this in your next query:

FROM parcela
WHERE ST_Area(geom) > 500; -- this returns parcels greater than average

Another approach, performing this in one step is the following:

    (SELECT SUM(ST_Area(geom))/count(*) as avg_area 
     FROM parcela) as x,
    ST_Area(geom) > x.avg_area
share|improve this answer
Yes, I thought for a moment that I could try it like that but I haven't tried it. I've tried it now and it works. Thank you very much. But if somebody still knows how to do it in one query I would appreciate it. Because I'm still not quite sure why my query is wrong? :) – user14026 Jan 10 '13 at 22:33
Just added a way of doing it in one step. – djq Jan 10 '13 at 22:35
It works perfectly now! Thank you very much! You helped me alot. – user14026 Jan 10 '13 at 22:38

Here is a window function alternative:

  SELECT OBJECTID, ST_Area(geom) > avg(ST_Area(geom)) OVER () AS filter
  FROM parcela
) AS ss
WHERE filter;

Note: I've replaced Sum(ST_Area(geom))/count(*) with a more readable avg(ST_Area(geom)).

With a window function, you have more power to your query, such as find all the parcels greater than the average area for each (e.g.) land_use:

  SELECT OBJECTID, ST_Area(geom) > avg(ST_Area(geom)) OVER (PARTITION BY land_use) AS filter
  FROM parcela
) AS ss
WHERE filter;
share|improve this answer
Thanks. This is very useful too. I appreciate it. – user14026 Jan 11 '13 at 8:15

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.