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


This thread ...


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