MIDRANGE dot COM Mailing List Archive

Rollup SQL function for v5r4?


I'm looking for a way to perform the equivalent of SQL's rollup function
or grouping sets in V5R4; showing detail and total in the same query.
The work around is to union the detail with a total line. However, one
must sort the entire union, not just one select statement, resulting
that the total line may not be at the bottom of the results. If I leave
the selections unsorted, the total will show at the bottom, but the
detail may not necessarily be sorted.

Not pressing, just a curiosity.

with temp as (

select part, sum(amount) as loadamount

from s9abs where account in ('4512','4513')

group by part


temp2 as (

select item, sum(absorptionlaborburdenallocation) as workamount,

sum(absorptionlaborburdenallocation) - loadamount as difference,

round(sum(absorptionlaborburdenallocation) - loadamount,2) as diffround

from s9abswork

join temp on (item=part)

group by item, loadamount


select item,workamount,loadamount,difference,diffround from temp2


select 'Total' as item,sum(workamount) as workamount,sum(loadamount) as
loadamount,sum(difference) as difference,sum(diffround) as diffround
from temp2

Loyd Goodbar

Business Systems

BorgWarner Shared Services


Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2015 by MIDRANGE dot 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 here. If you have questions about this, please contact