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
