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

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.