SQL - Return extra 'ALL' value for each group by

[DrSmyth]

Hi, I've an interesting SQL problem that's quite tricky to explain.... Here's goes:
I've an aggregate query that aggregates by each combination of the dimensions that have been selected, here's my SQL:

select DGOG.REGION_CDE, DGOG.CTRY_CDE, DFAC.BASE_ENTITY, DCPA.CUST_SUST_RTNG,
DFAC.FACIL_SUST_RAT, 
SUM(UW_APP_LMT_AMT_USD/CPCO.GCDU_COUNT) AS APPRVD_LMTS, count(distinct dcpa.gcdu_id) as DIST_CUST_COUNT,
COUNT(DISTINCT dfac.FACILITY_ID||dfac.BANK_APP_ID) AS DIST_FACIL_COUNT
FROM
INS_CDM_DFACILITY DFAC
INNER JOIN
INX_CDM_DENTITY DENT
ON
DFAC.base_entity = dent.base_entity
INNER JOIN
INS_CDM_DFAC_CPCOUNT CPCO
ON
DFAC.FACILITY_ID = CPCO.FACILITY_ID
AND
DFAC.BANK_APP_ID = CPCO.BANK_APP_ID
INNER JOIN
INS_cdm_dfac_cparty DFCP
ON
DFAC.FACILITY_ID = DFCP.FACILITY_ID
AND
DFAC.BANK_APP_ID = DFCP.BANK_APP_ID
INNER JOIN
INS_CDM_DCPARTY DCPA 
ON
DFCP.GCDU_ID = DCPA.GCDU_ID
INNER JOIN
INX_CDM_DGEOG_HIER DGOG
ON
DENT.CTRY_OF_RES = DGOG.CTRY_CDE
group by
DGOG.REGION_CDE, DGOG.CTRY_CDE, DFAC.BASE_ENTITY, DCPA.CUST_SUST_RTNG,
DFAC.FACIL_SUST_RAT
WITH UR;

As you can see, it groups by DGOG.REGION_CDE, DGOG.CTRY_CDE, DFAC.BASE_ENTITY, DCPA.CUST_SUST_RTNG,
DFAC.FACIL_SUST_RAT. However, I also want it to group by where each and every combination of these items is an 'ALL'. For eaxample, I want to group by each and every available CTRY_CDE and I also want to group by ALL CTRY_CDE, in combination with every other item.

I can do this by creating 20 queries and inserting the results into a table, but was wondering if there was a way I can do this with a single query...
Told you it was hard to explain!!!

 

[blom0344]

I am too busy right now to dive into this, but did you check on Graeme Birchall's cookbook entry on grouping sets?
That was the first thing that came to my mind. Not sure if that takes care for you, but it is worth a check:

http://mysite.verizon.net/Graeme_Birchall/id1.html

Ties Blom

 

[DrSmyth]

Cheers, mate... The rollup function mentioned with Grouping sets seems to be doing the kind of thing I'm after so gonna take a closer look at it..

Thanks,