Re: SQL listing with final totals

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