MIME-Version: 1.0
From: dfgpostgres <dfgpostgres3@gmail.com>
Date: Tue, 18 Feb 2025 15:56:27 -0500
Message-ID: <CAAcmDX8Xs5+NbSV1UGUsj06yQpC=wYj_FOfTMZNvFe6uXDTqcw@mail.gmail.com>
Subject: How to select avg(select max(something) from ...)
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000008f0667062e70e0f0"
Archived-At: <https://www.postgresql.org/message-id/CAAcmDX8Xs5%2BNbSV1UGUsj06yQpC%3DwYj_FOfTMZNvFe6uXDTqcw%40mail.gmail.com>
Precedence: bulk

--0000000000008f0667062e70e0f0
Content-Type: text/plain; charset="UTF-8"

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.

--0000000000008f0667062e70e0f0
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: base64

PGRpdiBkaXI9Imx0ciI+SGk6PGRpdj5wc3FsIDE1LjM8L2Rpdj48ZGl2Pjxicj48L2Rpdj48ZGl2
PkkgaGF2ZSBhIHRhYmxlIHdpdGggc2V0cyBvZiBvYnNlcnZhdGlvbnMsIGVhY2ggc2V0IHNoYXJp
bmcgYW4gaWQuPC9kaXY+PGRpdj5JIHdhbnQgdG8gZ2V0IHRoZSBhdmVyYWdlIG9mIHRoZSBtYXgg
b2YgZWFjaCBzZXQuPC9kaXY+PGRpdj48YnI+PC9kaXY+PGRpdj5pZMKgIHzCoCB2YWw8L2Rpdj48
ZGl2Pi0tLS0tLS0tLS0tPC9kaXY+PGRpdj4xwqAgwqAgwqA1LjA8L2Rpdj48ZGl2PjHCoCDCoCDC
oDQuMzwvZGl2PjxkaXY+McKgIMKgIMKgMy44PC9kaXY+PGRpdj4ywqAgwqAgwqA0Ljg8L2Rpdj48
ZGl2PjLCoCDCoCDCoDYuMDwvZGl2PjxkaXY+MsKgIMKgIMKgMi45PC9kaXY+PGRpdj4zwqAgwqAg
wqA0LjE8L2Rpdj48ZGl2PjPCoCDCoCDCoDQuNDwvZGl2PjxkaXY+M8KgIMKgIMKgOC4wPC9kaXY+
PGRpdj48YnI+PC9kaXY+PGRpdj5TbyBJIHdhbnQgdGhlIGF2ZyBvZiB0aGUgbWF4IG9mIHRoZSBz
ZXQgd2hlcmUgaWQ9MSAoNS4wKSwgd2hlcmUgaWQ9MiAoNi4wKSwgd2hlcmUgaWQ9MyAoOC4wKSB+
PSA2LjMzLi4uPC9kaXY+PGRpdj48YnI+PC9kaXY+PGRpdj5JIHRyaWVkIHRoaXMuLi48L2Rpdj48
ZGl2PjxkaXY+PGJyPjwvZGl2PjxkaXY+wqAgwqAgwqAgwqAgwqAgwqAgwqAgc2VsZWN0IDxicj7C
oCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoGF2Zyh4Lm1heHN6KSA8YnI+wqAgwqAgwqAgwqAgwqAg
wqAgwqAgwqBmcm9tPGJyPsKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgZHZtLmR2bV9ldmVudHMg
ZCw8YnI+wqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAgwqAoc2VsZWN0IGNhc3QobWF4KHNpemVfZykg
YXMgaW50KSBhcyBtYXhzesKgPC9kaXY+PGRpdj7CoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDC
oGZyb20gZHZtLndhX2R1X3Byb2ZpbGVfZGF0YcKgPC9kaXY+PGRpdj7CoCDCoCDCoCDCoCDCoCDC
oCDCoCDCoCDCoCDCoHdoZXJlIGR2bV9pZD1kLmR2bV9pZCkgeDxicj7CoCDCoCDCoCDCoCDCoCDC
oCDCoCDCoHdoZXJlPC9kaXY+PGRpdj7CoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCDCoCBkLnByb2pl
Y3Q9JiMzOTtmb28mIzM5OyBhbmTCoMKgPGJyPsKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgIMKgJmx0
O21vcmUgY29uZGl0aW9ucyBvbiBkJmd0OzwvZGl2PjxkaXY+PGJyPjwvZGl2PjxkaXY+SXQgZG9l
c24mIzM5O3QgbGlrZSB0aGF0IHJlZmVyZW5jZSB0byAmcXVvdDtkLmR2bV9pZCkgaW4gdGhhdCBz
dWJxdWVyeS48L2Rpdj48L2Rpdj48L2Rpdj4NCg==
--0000000000008f0667062e70e0f0--