Re: SQL Question running totals

Rob that is really neat and I have added to my examples database.

But not what I was after - I was looking for the sub totals to be on the
same line like a bank statement.

So you would have;

Amount Running total


And not sure I could achieve that with grouping sets ?

Thanks



Don Brown






From: "Rob Berendt" <rob@xxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Date: 12/12/2017 11:13 PM
Subject: Re: SQL Question running totals
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



That's really old.
Replace all // with double dashes. I changed them to // because email
readers often truncate after double dashes. It's how you chop off that
signature your work may force you to have.

// category: My Samples
// description: Subtotals - With Grouping sets
//
// Sample with "Detail", subtotal, grand total
// The trick is the detail is really a subtotal for each row
// If you don't want detail, omit
// Any columns selected which are not part of any grouping set, or
aggregate (like sum)
// The grouping set below flagged as "Detail"
//
// For information on output_queue_entries check out
// http://ibm.biz/DB2foriServices
//
SELECT OUTPUT_QUEUE_LIBRARY_NAME,OUTPUT_QUEUE_NAME, SPOOLED_FILE_NAME,
sum(SIZE) as size, sum(TOTAL_PAGES) as pages, JOB_NAME,
CREATE_TIMESTAMP
FROM QSYS2.OUTPUT_QUEUE_ENTRIES
group by grouping sets (
(OUTPUT_QUEUE_LIBRARY_NAME,OUTPUT_QUEUE_NAME, SPOOLED_FILE_NAME,
JOB_NAME, CREATE_TIMESTAMP) // "Detail"
,(OUTPUT_QUEUE_LIBRARY_NAME,OUTPUT_QUEUE_NAME) // subtotal
,() // Grand Total
)
order by OUTPUT_QUEUE_LIBRARY_NAME,OUTPUT_QUEUE_NAME, SPOOLED_FILE_NAME,

JOB_NAME, CREATE_TIMESTAMP;


Rob Berendt