Re: SQL listing with final totals

[Charles Wilt <charles.wilt@xxxxxxxxx>]

> 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