public inbox for [email protected]
help / color / mirror / Atom feedRe: How to select avg(select max(something) from ...)
2+ messages / 2 participants
[nested] [flat]
* Re: How to select avg(select max(something) from ...)
@ 2025-02-18 22:17 Adrian Klaver <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Adrian Klaver @ 2025-02-18 22:17 UTC (permalink / raw)
To: dfgpostgres <[email protected]>; [email protected]
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]
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: How to select avg(select max(something) from ...)
@ 2025-02-19 00:10 Greg Sabino Mullane <[email protected]>
parent: Adrian Klaver <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Greg Sabino Mullane @ 2025-02-19 00:10 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: dfgpostgres <[email protected]>; [email protected]
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-02-19 00:10 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-18 22:17 Re: How to select avg(select max(something) from ...) Adrian Klaver <[email protected]>
2025-02-19 00:10 ` Greg Sabino Mullane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox