Re: How to select avg(select max(something) from ...)

On Wed, 19 Feb 2025 at 09:56, dfgpostgres <dfgpostgres3(at)gmail(dot)com> wrote:
> So I want the avg of the max of the set where id=1 (5.0), where id=2 (6.0), where id=3 (8.0) ~= 6.33...
>
> I tried this...
>
> select
> avg(x.maxsz)
> from
> dvm.dvm_events d,
> (select cast(max(size_g) as int) as maxsz
> from dvm.wa_du_profile_data
> where dvm_id=d.dvm_id) x
> where
> d.project='foo' and
> <more conditions on d>
>
> It doesn't like that reference to "d.dvm_id) in that subquery.

You could use LATERAL before the subquery in the FROM clause, or you
could adjust the subquery by removing the "where dvm_id=d.dvm_id"
replacing it with GROUP BY dvm_id and adding that column to the SELECT
list and include that in the join condition between the tables.

David