PostgreSql query problem with aggregate functions

Question

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

SELECT OBJECTID 
FROM parcela
WHERE ST_Area(geom)<(SELECT Sum(ST_Area(geom)/count(*)))
GROUP BY OBJECTID;

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

Answer 1

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:

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

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

SELECT 
    OBJECTID,
    x.avg_area 
FROM 
    (SELECT SUM(ST_Area(geom))/count(*) as avg_area 
     FROM parcela) as x,
    parcela
WHERE
    ST_Area(geom) > x.avg_area

Answer 2

Here is a window function alternative:

SELECT OBJECTID
FROM (
  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
FROM (
  SELECT OBJECTID, ST_Area(geom) > avg(ST_Area(geom)) OVER (PARTITION BY land_use) AS filter
  FROM parcela
) AS ss
WHERE filter;