public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: dfgpostgres <[email protected]>
To: [email protected]
Subject: Re: How to select avg(select max(something) from ...)
Date: Tue, 18 Feb 2025 14:17:52 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAAcmDX8Xs5+NbSV1UGUsj06yQpC=wYj_FOfTMZNvFe6uXDTqcw@mail.gmail.com>
References: <CAAcmDX8Xs5+NbSV1UGUsj06yQpC=wYj_FOfTMZNvFe6uXDTqcw@mail.gmail.com>

On 2/18/25 12:56, dfgpostgres wrote:
> Hi:
> psql 15.3
> 
> I have a table with sets of observations, each set sharing an id.
> I want to get the average of the max of each set.
> 
> id  |  val
> -----------
> 1     5.0
> 1     4.3
> 1     3.8
> 2     4.8
> 2     6.0
> 2     2.9
> 3     4.1
> 3     4.4
> 3     8.0
> 
> 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.


create table wa_du_profile_data (id integer, val float);

insert into wa_du_profile_data values (1, 5.0),
(1, 4.3),
(1, 3.8),
(2, 4.8),
(2, 6.0),
(2, 2.9),
(3, 4.1),
(3, 4.4),
(3, 8.0);


with max_val as (select max(val) from wa_du_profile_data group by id) 
select avg(max) from max_val;

6.333333333333333

-- 
Adrian Klaver
[email protected]

view thread (2+ messages)  latest in thread

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: <[email protected]>

* 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