Re: How can I get a query-based subtotal in a select using group by rollup ?
| От | David G. Johnston |
|---|---|
| Тема | Re: How can I get a query-based subtotal in a select using group by rollup ? |
| Дата | |
| Msg-id | CAKFQuwbq-SUyW11qvtALAPP+rkW-pEByNV_dQ_mS9GbC3QrKGA@mail.gmail.com обсуждение исходный текст |
| Ответ на | How can I get a query-based subtotal in a select using group by rollup ? (dfgpostgres <dfgpostgres3@gmail.com>) |
| Список | pgsql-general |
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.
В списке pgsql-general по дате отправления: