× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.