Grouping sets, cube, rollup functionality

Grouping sets, cube, rollup functionality

Post by Pete » Sat, 28 Jun 2003 03:00:58



Hi all;
We are trying to learn and leverage the "new" summary/grouping SQL
syntax in our environment (DB2 ESE V8 SP2 Win2000). Here's and example
of a typical query:
----------------------------------------------------------
With temp(dmMonthNum,dmYearNum,dmDayNum,agtnamelfm,HrCnt,TransactionDurationMin,cnt)
as
(
SELECT dmMonthNum,dmYearNum,dmDayNum,agtnamelfm,
Count(distinct dmhourdesc),
cast(sum(TrxD.trxdetaildursec)/60 as REAL),
count (*)
FROM         phazzard.fct_trxdetail trxD
inner JOIN phazzard.dmn_transtype tt ON tt.transtypekeyid =
trxD.transtypekeyid
inner join phazzard.dmn_facility f on TrxD.facilitykeyid =
f.facilitykeyid
inner JOIN phazzard.dmn_location L on L.LocationKeyID =
TrxD.LocationKeyID
inner JOIN phazzard.dmn_inclination I on  I.inclinationkeyid =
TrxD.inclinationkeyid
inner join phazzard.dmn_disposition d on d.dispositionkeyid =
TrxD.dispositionkeyid
inner join phazzard.dmn_datetime dt on dt.timekeyid = TrxD.timekeyid
inner join phazzard.dmn_agent A on TrxD.agtcontactkeyid =
A.agtcontactkeyid
where
transtypedesc = 'Triage Call'
group by (dmMonthNum,dmYearNum,dmDayNum,agtnamelfm)
)
Select dmMonthNum,dmYearNum,agtnamelfm, sum(HrCnt) as TotalHours,
sum(TransactionDurationMin)/sum(cnt) as AveCallDuration, sum(cnt) as
CallCount,
cast(sum(cnt) as real)/cast(sum(HrCnt) as real) as CallsPerHour
from temp
Group by (dmMonthNum,dmYearNum,agtnamelfm)
order by agtnamelfm
---------------------------------------------------

This accesses a star schema and produces a very powerful and flexible
report. Is there a way using cube/rollup/grouping sets to replace the
temp table? The temp table gets data at a daily level and the second
query "rolls up" and calculates at a monthly level. Any input is very
much appreciated. Thanks!

Pete H

 
 
 

1. DBMS with group by cube() / group by rollup(), recursive statements

Hello out there,

does someone know a database management system, which can serve queries
with group by cube() / group by rollup(), recursive queries and is free
for educational use?

I don't want to download and install the trial of IBM DB2 (it's big (545
MB) and does a lot with the system while being installed, I've been
told).

Thanks,
Marcel

2. Float numbers update in client side cursors.

3. group by vs rollup vs cube

4. left outer join

5. CUBE or ROLLUP option in GROUP BY statement

6. 1 LeftWords Function & 2 Time calculations

7. Rollup of a Cube with lower level calcs

8. ASP and Filemaker insanity

9. Custom rollup formulas and virtual cubes

10. Failed to update custom rollup for cube levels

11. custom rollup but in 1 cube only ?

12. Rollup/Cube and Count

13. ROLLUP OR CUBE?