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

• user14026: 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? :)
• djq: Just added a way of doing it in one step.
• user14026: It works perfectly now! Thank you very much! You helped me alot.

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;