Re: How to select avg(select max(something) from ...) - Mailing list pgsql-general

On Wed, 19 Feb 2025 at 09:56, dfgpostgres <dfgpostgres3@gmail.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