Re: SQL listing with final totals

On 3/28/2017 4:03 PM, Steinmetz, Paul wrote:

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

That's because raw SQL isn't really a reporting tool, and what you want
is a report :-/

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

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?

Yes. UNION brings two identically laid out tables together into one
result. The key here is 'identical' - same number of columns, and each
column needs to be the same data type. So...

SELECT OUTQ, SPOOLNAME, size, pages, job_name, created
FROM QSYS2.OUTPUT_QUEUE_ENTRIES
union
SELECT ' ' as outq, ' ' as spoolname, 0 as size, sum(pages) as pages,
' ' as job_name, current_timestamp as created
FROM QSYS2.OUTPUT_QUEUE_ENTRIES
ORDER BY 3 desc;



That's OK, but it sorts the total line into the wrong place. I
typically add a 'row type' column:

SELECT '1' as row_type, OUTQ, SPOOLNAME, size, pages, job_name, created
FROM QSYS2.OUTPUT_QUEUE_ENTRIES
union
SELECT '9' as row_type, ' ' as outq, ' ' as spoolname, 0 as size,
sum(pages) as pages,
' ' as job_name, current_timestamp as created
FROM QSYS2.OUTPUT_QUEUE_ENTRIES
ORDER BY 1, 4 desc;

But you could use this as a CTE that you can then SELECT from without
showing the row type.