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
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
BorgWarner Shared Services