PostgreSQL get minimum of count(*)?

Question

I need to get the minimum count of data. my current sql is

select min(count(*)) from table group by id ;

where i expect to get minimum count of variable count(*). Seems like above query is not allowed. Error

aggregate function calls cannot be nested

is thrown. Is there a way achieve this using any alternating method ?

Answer 1

Many solutions exist as attested by the number of answers here. Interestingly, @ahorse_with_no_name questions the performance of each of them.

The subquery approach

SELECT MIN(count) FROM (SELECT COUNT(*) FROM table GROUP BY id) t;

It typically yields the following plan:

Aggregate
   ->  HashAggregate
         ->  Seq Scan on table

The ORDER/LIMIT approach

SELECT COUNT(*) FROM table GROUP BY id ORDER BY 1 LIMIT 1;

It feels more natural to some but unfortunately yields a second sort:

 Limit
   ->  Sort
         Sort Key: (count(*))
         ->  HashAggregate
              ->  Seq Scan on table

The CTE approach

WITH cte AS (SELECT count(*) FROM table GROUP BY id) SELECT MIN(count) FROM cte;

It is very similar to the subquery, except that the plan shows the CTE is scanned (and could be materialized if the table is large).

 Aggregate
   CTE cte
     ->  HashAggregate
           ->  Seq Scan on table
   ->  CTE Scan on cte

The window aggregate function approach

Alternatively, you could use a window aggregate function combined with LIMIT and just avoid the second sort.

SELECT MIN(COUNT(*)) OVER () FROM table GROUP BY id LIMIT 1;

It yields a plan equivalent to the subquery approach (if we consider LIMIT 1 as nearly free).

 Limit
   ->  WindowAgg
         ->  HashAggregate
               ->  Seq Scan on table