Re: Help with list records and getting totals after the breaks.

[Peter Dow <petercdow@xxxxxxxxx>]

Leaving out the ROLLUP keyword gives *only* the totals asked for.

How about

with t1 as
 (
 SELECT co, account, invoice#, sum(amount) totamt
  fROM pdow/dftest
 GROUP BY ROLLUP (co, account, invoice#)
 ORDER BY co, account, invoice#
 )
 select case when invoice# is null
             then 'Total' else '' end as type,
        co, account, coalesce(invoice#,'') invoice, totamt
   from t1 where account is not null;

TYPE    CO    ACCOUNT INVOICE    TOTAMT
        AA    1234    Inv1        10.00
        AA    1234    Inv2        15.00
Total   AA    1234                25.00
        BB    5678    Inv3        20.00
        BB    5678    Inv5        13.00
Total   BB    5678                33.00


On 11/2/2022 2:04 AM, Niels Liisberg wrote:
> Yes - simply leave out the "ROLLUP" keyword in that case
>
> On Mon, Oct 31, 2022 at 9:12 PM Peter Dow<petercdow@xxxxxxxxx>  wrote:
>
> > Hi Darryl,
> >
> > I adapted the following from Example 3 at
> >
> > https://www.ibm.com/docs/en/db2-for-zos/11?topic=subselect-examples-grouping-sets-rollup-cube-queries
> >
> > SELECT co, account, invoice#, sum(amount)
> >     FROM MyFile
> >    GROUP BY ROLLUP (co, account, invoice#)
> >    ORDER BY co, account, invoice#;
> >
> > Co   Account    Invoice#    Amount
> > AA    1234        Inv1       10.00
> > AA    1234        Inv2       15.00
> > AA    1234        -          25.00
> > AA    -           -          25.00
> > BB    5678        Inv3       20.00
> > BB    5678        Inv5       13.00
> > BB    5678        -          33.00
> > BB    -           -          33.00
> > -     -           -          58.00
> >
> > I'm not sure if there's a way to eliminate the totals you don't want.