How can I get a query-based subtotal in a select using group by rollup ?
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 ...)
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.
Re: PG Unpivot ?
Wow ! That did it, even with the predicate I stuck on the end when I tried it. Thanks Depesz ! On Mon, Nov 3, 2025 at 12:22 PM hubert depesz lubaczewski wrote: > On Mon, Nov 03, 2025 at 12:18:55PM -0500, dfgpostgres wrote: > > psql (13.2, server 15.3) on linux > > > > I think they call this "unpivot" in MSSQL ? > > > > How can I get an sql query to return one line per column with... an ID, > > column name and value. the ctid for the id field is fine. > > > > Example: > > dvdb=# create table unpivot (intcol integer, floatcol float, strcol > > varchar); > > CREATE TABLE > > dvdb=# insert into unpivot (intcol,floatcol,strcol) values > > (1,1.1,'one'),(2,2.2,'two'),(3,3.3,'three'); > > INSERT 0 3 > > dvdb=# select * from unpivot; > > intcol | floatcol | strcol > > +--+ > > 1 | 1.1 | one > > 2 | 2.2 | two > > 3 | 3.3 | three > > (3 rows) > > I want 9 records returned, each row with 3 cols, 1st col is the ctid, > > second is the column name, third is the val. > > I think it should work: > > select u.ctid, e.* from unpivot u, to_jsonb(u) j, jsonb_each_text(j) e; > > Best regards, > > depesz > >
PG Unpivot ?
psql (13.2, server 15.3) on linux I think they call this "unpivot" in MSSQL ? How can I get an sql query to return one line per column with... an ID, column name and value. the ctid for the id field is fine. Example: dvdb=# create table unpivot (intcol integer, floatcol float, strcol varchar); CREATE TABLE dvdb=# insert into unpivot (intcol,floatcol,strcol) values (1,1.1,'one'),(2,2.2,'two'),(3,3.3,'three'); INSERT 0 3 dvdb=# select * from unpivot; intcol | floatcol | strcol +--+ 1 | 1.1 | one 2 | 2.2 | two 3 | 3.3 | three (3 rows) I want 9 records returned, each row with 3 cols, 1st col is the ctid, second is the column name, third is the val. Thanks in Advance !
