Thread

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

   dfgpostgres <dfgpostgres3@gmail.com> — 2025-02-18T20:56:27Z

   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.
   

2. Re: How to select avg(select max(something) from ...)

   David Rowley <dgrowleyml@gmail.com> — 2025-02-18T21:06:21Z

   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
   
   
   
   

3. Re: How to select avg(select max(something) from ...)

   Adrian Klaver <adrian.klaver@aklaver.com> — 2025-02-18T22:17:52Z

   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
   
   
   
   
   

4. Re: How to select avg(select max(something) from ...)

   Greg Sabino Mullane <htamfids@gmail.com> — 2025-02-19T00:10:06Z

   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