Re: SQL listing with final totals

From what I've seen...

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