MIME-Version: 1.0
In-Reply-To: <CAAcmDX-cZmNu9jHzu7XT__VCsGGF-yC0D7T653-cN8p3NZhUVA@mail.gmail.com>
References: <CAAcmDX-cZmNu9jHzu7XT__VCsGGF-yC0D7T653-cN8p3NZhUVA@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Wed, 7 Aug 2024 18:42:46 -0700
Message-ID: <CAKFQuwbq-SUyW11qvtALAPP+rkW-pEByNV_dQ_mS9GbC3QrKGA@mail.gmail.com>
Subject: Re: How can I get a query-based subtotal in a select using group by
 rollup ?
To: dfgpostgres <dfgpostgres3@gmail.com>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000ccea95061f2224f5"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwbq-SUyW11qvtALAPP%2BrkW-pEByNV_dQ_mS9GbC3QrKGA%40mail.gmail.com>
Precedence: bulk

--000000000000ccea95061f2224f5
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=3Drt.regression_name and domain_name=3Drt.domain_name and
> dispatch_status=3D'Y') as dispatched
>   from
>     regr.dispatch_tracker rt where  rt.regression_name=3D'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=3DY
in the group:  count(*) filter (where dispatch_status =3D =E2=80=98Y=E2=80=
=99)

The option that avoids the subquery is arguably better.  Though I=E2=80=99d
probably still include the regression_name in the output anyway - why hide
what you are filtering on.

David J.

--000000000000ccea95061f2224f5
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wednesday, August 7, 2024, dfgpostgres &lt;<a href=3D"mailto:dfgpostgres=
3@gmail.com">dfgpostgres3@gmail.com</a>&gt; wrote:<br><blockquote class=3D"=
gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-=
left:1ex"><div dir=3D"ltr"><div><br></div><div>=C2=A0 (select<br>=C2=A0 =C2=
=A0 domain_name,<br>=C2=A0 =C2=A0 sum(total_tests) as total_tests,<br>=C2=
=A0 =C2=A0 sum(tests_completed) as tests_completed,<br>=C2=A0 =C2=A0 sum(te=
sts_passed) as tests_passed,<br>=C2=A0 =C2=A0 sum(tests_failed) as tests_fa=
iled,<br><span style=3D"background-color:rgb(255,255,255)">=C2=A0 =C2=A0<fo=
nt color=3D"#0000ff"> (select count(*) from dispatch_tracker where regressi=
on_name=3Drt.regression_<wbr>name and domain_name=3Drt.domain_name and disp=
atch_status=3D&#39;Y&#39;) as dispatched</font></span><br>=C2=A0 from <br>=
=C2=A0 =C2=A0 regr.dispatch_tracker rt where =C2=A0rt.regression_name=3D=
9;2024_08_<wbr>02_10_32_53_soundwave__er_<wbr>common_regression__CL2017473_=
<wbr>z1_soundwave_adm&#39;<br>=C2=A0 group by rollup(rt.domain_name) order =
by rt.domain_name ASC NULLS LAST) d;</div></div></blockquote><div><br></div=
><div>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 whe=
re clause filter) and domain_name just count the number of dispatch_status=
=3DY in the group: =C2=A0count(*) filter (where dispatch_status =3D =E2=80=
=98Y=E2=80=99)</div><div><br></div><div>The option that avoids the subquery=
 is arguably better.=C2=A0 Though I=E2=80=99d probably still include the re=
gression_name in the output anyway - why hide what you are filtering on.</d=
iv><div><br></div><div>David J.</div><div><br></div>

--000000000000ccea95061f2224f5--