How can I get a query-based subtotal in a select using group by rollup ?

[dfgpostgres]

pg 15.3 on linux

I don't even know if the title makes sense, I think it's better explained
with an example

This is the table...

dvdb=# \d dispatch_tracker
  Table "regr.dispatch_tracker"
   Column|   Type   | Collation | Nullable |
Default
-+--+---+--+-
 regression_name | character varying|   |  |
 domain_name | character varying|   |  |
 dispatch_status | character varying|   |  |
 total_tests | integer  |   |  |
 tests_running   | integer  |   |  |
 tests_completed | integer  |   |  |
 tests_passed| integer  |   |  |
 tests_failed| integer  |   |  |

And this query

select
  d.domain_name,
  d.total_tests,
  d.tests_completed,
  d.tests_passed,
  d.tests_failed,
  round(d.tests_completed*100.0/d.total_tests) as perc_tot_compl,
  round(d.tests_passed*100.0/d.tests_completed) as perc_compl_pass,
  round(d.tests_failed*100.0/d.tests_completed) as perc_compl_fail,
  round(d.tests_passed*100.0/d.total_tests) as perc_pass
from
  (select
domain_name,
sum(total_tests) as total_tests,
sum(tests_completed) as tests_completed,
sum(tests_passed) as tests_passed,
sum(tests_failed) as tests_failed,
  from
regr.dispatch_tracker rt where
 
rt.regression_name='2024_08_02_10_32_53_soundwave__er_common_regression__CL2017473_z1_soundwave_adm'
  group by rollup(rt.domain_name) order by rt.domain_name ASC NULLS LAST) d;

... generates something like this...

 domain_name | total_tests | tests_completed | tests_passed | tests_failed
| perc_tot_compl | perc_compl_pass | perc_compl_fail | perc_pass
-+-+-+--+--++-+-+---
 ACP |  87 |  82 |1 |   81
| 94 |   1 |  99 | 1
 CDP |  28 |  27 |0 |   27
| 96 |   0 | 100 | 0
 COH | 102 |  67 |0 |   67
| 66 |   0 | 100 | 0
 DCN | 181 | 180 |5 |  175
| 99 |   3 |  97 | 3
 DFD | 458 | 292 |   25 |  267
| 64 |   9 |  91 | 5
 DFT |1302 | 830 |0 |  830
| 64 |   0 | 100 | 0
 GDP | 413 | 308 |   29 |  279
| 75 |   9 |  91 | 7
 GFX |  96 |  72 |1 |   71
| 75 |   1 |  99 | 1
 INT |   9 |   2 |0 |2
| 22 |   0 | 100 | 0
 IPU |  24 | |  |
 || | |
 IPU_SANITY  |   2 | |  |
 || | |
 OSS |  43 |  43 |0 |   43
|100 |   0 | 100 | 0
 PWR | 535 | 207 |1 |  206
| 39 |   0 | 100 | 0
 SEC | 172 | 128 |3 |  125
| 74 |   2 |  98 | 2
 UMSCH   |  16 | |  |
 || | |
 VPE | 130 | 125 |1 |  124
| 96 |   1 |  99 | 1
 |3598 |2363 |   66 | 2297
| 66 |   3 |  97 | 2
(17 rows)

Now, I want to add a new column that's a subset of the "total_tests"
value.  Specifically, I want to add a column called "dispatched" which is
just the number of total_tests which have column "dispatch_status" = 'Y'

This blows up, but shows the intent...

select
  d.domain_name,
  d.total_tests,
  d.tests_completed,
  d.tests_passed,
  d.tests_failed,
  d.dispatched,
  round(d.tests_completed*100.0/d.total_tests) as perc_tot_compl,
  round(d.tests_passed*100.0/d.tests_completed) as perc_compl_pass,
  round(d.tests_failed*100.0/d.tests_completed) as perc_compl_fail,
  round(d.tests_passed*100.0

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

[dfgpostgres]

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
 

It doesn't like that reference to "d.dvm_id) in that subquery.