MIME-Version: 1.0
References: <CAAcmDX8Xs5+NbSV1UGUsj06yQpC=wYj_FOfTMZNvFe6uXDTqcw@mail.gmail.com>
In-Reply-To: <CAAcmDX8Xs5+NbSV1UGUsj06yQpC=wYj_FOfTMZNvFe6uXDTqcw@mail.gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Wed, 19 Feb 2025 10:06:21 +1300
Message-ID: <CAApHDvoDbQo0AsVXpcvKzy_p_YJ6bRyNEGNEz_mE4rNhtkfg0g@mail.gmail.com>
Subject: Re: How to select avg(select max(something) from ...)
To: dfgpostgres <dfgpostgres3@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAApHDvoDbQo0AsVXpcvKzy_p_YJ6bRyNEGNEz_mE4rNhtkfg0g%40mail.gmail.com>
Precedence: bulk

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