RE: SQL listing with final totals -- MIDRANGE-L

I want to thank everyone for their input.
Slightly modify Charles example below, changing the order by sum(pages) desc
Returns a listing of all outqs, sorted by largest on top.

SELECT output_queue_name,
output_queue_library_name, sum(size), sum(pages) FROM
QSYS2.OUTPUT_QUEUE_ENTRIES group by
rollup(output_queue_library_name,
output_queue_name) ORDER BY sum(pages) desc

Combining SQL, DB2 for I - Services, automating the process with AJS, emailing the results with Info Print Server.
Some very good IBM I daily housekeeping chores.
Where and how could create a line to share these?
New topic for Common?

I'm still deciding on where to keep the SQL source.

Paul

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Tuesday, March 28, 2017 7:26 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL listing with final totals

From what I've seen...

ROLLUP is a nice shortcut if you're dealing with summary data in the first place...

But if you want details and subtotals and/or totals, you hav eto build the grouping sets yourself.

Charles

On Tue, Mar 28, 2017 at 2:35 PM, Rob Berendt <rob@xxxxxxxxx> wrote:

This one does subtotals but no details SELECT output_queue_name,
output_queue_library_name, sum(size), sum(pages) FROM
QSYS2.OUTPUT_QUEUE_ENTRIES group by rollup(output_queue_library_name,
output_queue_name) ORDER BY output_queue_library_name,
output_queue_name ;

The key being GROUP BY ROLLUP

Still researching...

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

From: "Steinmetz, Paul" <PSteinmetz@xxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxx>
Date: 03/28/2017 04:03 PM
Subject: SQL listing with final totals
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>

I'm trying to add final totals to an SQL listing.
Struggling with the syntax a bit.

Below SQL lists all spoolfiles on the system, sorted descending by
size

SELECT OUTQ, SPOOLNAME, size, pages, job_name, created FROM
QSYS2.OUTPUT_QUEUE_ENTRIES ORDER BY SIZE desc

03/28/17 15:29:47 PAGE 1
OUTPUT_QUEUE_NAME SPOOLNAME SIZE PAGES JOB_NAME
CREATED
TRCALL QPCSMPRT 2,099,224 335,953
748135/PRODUCT/TRCTSYSOFF 2017-03-28-10.30.01.954172
TRCALL QSYSPRT 1,968,152 390,495
748135/PRODUCT/TRCTSYSOFF 2017-03-28-10.34.16.861769
TRCALL QPCSMPRT 1,198,104 195,109
751230/PRODUCT/TRCTSYSOFF 2017-03-28-15.00.02.391903
TRCALL QSYSPRT 1,132,568 229,525
751230/PRODUCT/TRCTSYSOFF 2017-03-28-15.02.32.520147
CM_CYCLE QSYSPRT 329,748 131,756
314250/CPAGDEPU/S001BIL807 2017-02-15-02.04.28.807613
CM_CYCLE QSYSPRT 329,748 128,507
604539/CPAKFRAN/S001BIL807 2017-03-15-01.57.58.844694
CM_CYCLE QSYSPRT 313,364 125,650
381119/CPAGDEPU/S001BIL807 2017-02-22-01.54.56.171381
CM_CYCLE QSYSPRT 313,364 124,582
703277/CPAGDEPU/S001BIL807 2017-03-24-01.52.58.137695
....

I have a 2nd SQL showing only the sum.

SELECT sum(pages)
FROM QSYS2.OUTPUT_QUEUE_ENTRIES
SELECT statement run complete.

SUM ( SIZE ) SUM ( PAGES )
489,542,320 81,492,452

What I would like to see is the listing with the sum at the bottom.
Do I need a union to do this?
Or are their other choices/options?
I was reading some links.

Thank You
_____
Paul Steinmetz
IBM i Systems Administrator

Pencor Services, Inc.
462 Delaware Ave
Palmerton Pa 18071

610-826-9117 work
610-826-9188 fax
610-349-0913 cell
610-377-6012 home

psteinmetz@xxxxxxxxxx
http://www.pencor.com/

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: http://amzn.to/2dEadiD