public inbox for [email protected]
help / color / mirror / Atom feedFrom: dfgpostgres <[email protected]>
To: [email protected]
Subject: How can I get a query-based subtotal in a select using group by rollup ?
Date: Wed, 7 Aug 2024 21:32:45 -0400
Message-ID: <CAAcmDX-cZmNu9jHzu7XT__VCsGGF-yC0D7T653-cN8p3NZhUVA@mail.gmail.com> (raw)
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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: How can I get a query-based subtotal in a select using group by rollup ?
In-Reply-To: <CAAcmDX-cZmNu9jHzu7XT__VCsGGF-yC0D7T653-cN8p3NZhUVA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox