Re: SQL listing with final totals

[Dan <dan27649@xxxxxxxxx>]

Pure gold, Birgitta!  Thanks for posting this!

- Dan

On Wed, Mar 29, 2017 at 1:32 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

> 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