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

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

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
adrian.klaver@aklaver.com

Another variation:

select avg(max) from (select distinct max(val) over(partition by id) from
mytable);

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support