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

[dfgpostgres <dfgpostgres3@xxxxxxxxx>]

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/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,
    (select count(*) from dispatch_tracker where regression_name=rt.regression_name and domain_name=rt.domain_name and dispatch_status='Y') as dispatched
  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;

The error...

ERROR:  subquery uses ungrouped column "rt.regression_name" from outer query
LINE 19: ...nt(*) from dispatch_tracker where regression_name=rt.regress...

Any help would be appreciated.

Thanks

-dave