Thread
-
How can I get a query-based subtotal in a select using group by rollup ?
dfgpostgres <dfgpostgres3@gmail.com> — 2024-08-08T01:32:45Z
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 -
Re: How can I get a query-based subtotal in a select using group by rollup ?
David G. Johnston <david.g.johnston@gmail.com> — 2024-08-08T01:42:46Z
On Wednesday, August 7, 2024, dfgpostgres <dfgpostgres3@gmail.com> wrote: > > (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; > Either add regression_name to the group by as the error hints at you, or since you are already grouping implicitly by that (by virtue of the where clause filter) and domain_name just count the number of dispatch_status=Y in the group: count(*) filter (where dispatch_status = ‘Y’) The option that avoids the subquery is arguably better. Though I’d probably still include the regression_name in the output anyway - why hide what you are filtering on. David J.