public inbox for [email protected]
help / color / mirror / Atom feedFrom: David Rowley <[email protected]>
To: dfgpostgres <[email protected]>
Cc: [email protected]
Subject: Re: How to select avg(select max(something) from ...)
Date: Wed, 19 Feb 2025 10:06:21 +1300
Message-ID: <CAApHDvoDbQo0AsVXpcvKzy_p_YJ6bRyNEGNEz_mE4rNhtkfg0g@mail.gmail.com> (raw)
In-Reply-To: <CAAcmDX8Xs5+NbSV1UGUsj06yQpC=wYj_FOfTMZNvFe6uXDTqcw@mail.gmail.com>
References: <CAAcmDX8Xs5+NbSV1UGUsj06yQpC=wYj_FOfTMZNvFe6uXDTqcw@mail.gmail.com>
On Wed, 19 Feb 2025 at 09:56, dfgpostgres <[email protected]> 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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: How to select avg(select max(something) from ...)
In-Reply-To: <CAApHDvoDbQo0AsVXpcvKzy_p_YJ6bRyNEGNEz_mE4rNhtkfg0g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox