Union versus RollUp versus Grouping Sets - Just a short explanation:
UNION
When using UNION several SELECT statements are performed and finally the result is merged.
This means the (same) data must be read multiple times. In this way performance slows down.
The trick when using UNION was to get the sub-totals at the right position, i.e. how to specify the ORDER BY clause.
Before Release 6.1 it was the only way to get sub-totals with SQL.
ROLLUP, CUBE, GROUPING SETS (Multi-dimensional grouping)
Were introduced with Release 6.1
Allow easily to return sub- and grand totals.
Data is read only once!! (Contrary to the UNION version)
In which sequence the data and sub-totals are returned depends on the specified ORDER BY Clause-
ROLLUP generates totals in the same way as QUERY/400.
Example: ROLLUP(Year, Customer, Item) -
Generates the following totals:
Year/Customer/Item
Year/Customer
Year
Grand Total
CUBE generates any possible sub-total and grand total
Example: CUBE(Year, Customer, Item)
Generates the following totals:
Year/Customer/Item
Year/Customer
Year/Item
Customer/Item
Year
Customer
Item
Grand Total
With GROUPING SETS the sub-totals and grand total to be returned can be specified
Example: GROUPING SETS((Year, Customer), (Year, Item), (Year), ())
Generates the following totals
Year/Customer
Year/Item
Year
Grand Total
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Mittwoch, 29. März 2017 01:05
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: SQL listing with final totals
Another option, subtotal by outq
SELECT OUTQ, SPOOLNAME, sum(size) as size, sum(pages) as pages, job_name, created FROM QSYS2.OUTPUT_QUEUE_ENTRIES group by grouping sets
((OUTQ, SPOOLNAME, job_name, created)
,(OUTQ)
,())
order by OUTQ, SPOOLNAME, job_name, created;
basically, the first group is detail, the second is subtotal by OUTQ, and the last give a grand total...
Charles
On Tue, Mar 28, 2017 at 4:48 PM, Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:
On Tue, Mar 28, 2017 at 2:37 PM, Buck Calabro <kc2hiz@xxxxxxxxx> wrote:
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 :-/
Well that's true...but with the new analytical capabilities built
into the DB...specifically grouping sets and super groups, the DB can
give Paul what he's asking for..
SELECT OUTQ, SPOOLNAME, sum(size) as size, sum(pages) as pages,
job_name, created FROM QSYS2.OUTPUT_QUEUE_ENTRIES group by grouping
sets
((OUTQ, SPOOLNAME, job_name, created
),())
order by OUTQ, SPOOLNAME, job_name, created;
Charles
--
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
midrange.com
