Re: SQL listing with final totals

Select
OUTQ,SPOOLNAME,JOB_NAME,CREATED,PAGES,SIZE,SPLFS
From
(Select
Coalesce(L1,L2,L3) As LEVEL,
Coalesce(OUTQ, '*ALL') As OUTQ,
Coalesce(SPOOLNAME, '----------') As SPOOLNAME,
Coalesce(JOB_NAME,'--------------------------') As JOB_NAME,
Coalesce(Varchar_format(CREATED,'YYYY-MM-DD HH:MI:SS'), '-------------------') As CREATED,
Sum(PAGES) As PAGES, Sum(SIZE) As SIZE, Count(1) As SPLFS

From QSYS2.OUTPUT_QUEUE_ENTRIES

Cross Join
(Values (1,2,3)) AS X (L1,L2,L3)
Group By Grouping Sets
((L1, OUTQ, SPOOLNAME, JOB_NAME, CREATED),
(L2, OUTQ),
(L3))) As O
Order By Nullif(OUTQ,'*ALL'), LEVEL, SIZE Desc, CREATED Desc;


This SQL statements lists all the Spoolfiles in decreasing size order with newest at the top for each output queue with a total line immediately after the detail spoolfiles for each output queue, containing total size, total pages and number of spoolfiles in the output queue, the other fields are filled with dashes.

Grand totals for all output queues is at the bottom.


Forcing the grand totals to the bottom required using sub-select so that a derived value could be used on the OUTQ column.


Jim