Problem w/ Group By Cube Operator

What would cause a SQL statement using the 'GROUP BY CUBE' syntax to return multiple aggregate totals or duplicate groupings with varying totals?

For Example:

CREATE TABLE region_cube AS
SELECT DECODE(GROUPING(region), 1, 'All Regions', region) AS Region,  DECODE(GROUPING(division), 1, 'All Divisions', division) AS Division,  DECODE(GROUPING(department), 1, 'All Departments', department) AS Department,
 SUM(sales) Sales
FROM region_info
GROUP BY CUBE (region, division, department);

Then...

SELECT *
FROM region_cube;

I Get..

Region Division Department Sales

All Regions     All Divisions   All Departments     9238643 <---?
All Regions     All Divisions   All Departments     686543  <---?
All Regions     All Divisions   All Departments     2454784 <---?
All Regions     All Divisions   All Departments     354654  <---?
All Regions     All Divisions   Dry Goods           32452    ?
All Regions     All Divisions   Dry Goods           65463    ?
All Regions     All Divisions   Dry Goods           34353    ?
All Regions     All Divisions   Whatever            32452
All Regions     All Divisions   Whatever            65463  
All Regions     All Divisions   Whatever            34353

.
.
.

Different Sales for the same groupings and every record within the table has duplicate groupings. Wha?

Now the actual SQL I'm using is somewhat more complicated but this is more of a theory question. The above example should be adequate to explain the problem.

Here is the actual SQL used to to create the table:

CREATE TABLE cube_monthly AS
SELECT
  DECODE(GROUPING(regionalvp), 1, 'ALL REGIONAL VPS', regionalvp) AS RegionalVP,
  DECODE(GROUPING(division), 1, 'ALL DIVISIONS', division) AS division,
  DECODE(GROUPING(lot), 1, 'ALL LOTS', Lot) AS Lot,   DECODE(GROUPING(underwriter), 1, 'ALL UNDERWRITERS', underwriter) AS   underwriter,
  DECODE(GROUPING(marketing_rep), 1, 'ALL MARKETING REPS', marketing_rep) AS marketing_rep,
  COUNT(*) "Total Apps/Accs",
  SUM(loan_amount + pts_financed) "Total Loan Amount",   SUM(rate)/(SUM(rate * (loan_amount + pts_financed))) "Weighted Rate",
  SUM(term)/(SUM(term * (loan_amount + pts_financed))) "Weighted Term",
  SUM(FICO)/(SUM(FICO * (loan_amount + pts_financed))) "Weighted FICO",
  SUM(dti_weighted)/(SUM(dti_weighted * (loan_amount + pts_financed))) "Weighted DTI" FROM oradba.cpm_report_stg1 WHERE TO_CHAR(TRUNC(application_date), 'MONTH') = TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, - 1)), 'MONTH') AND dti_weighted <> 0
GROUP BY CUBE (application_date, regionalvp, division, lot, underwriter, marketing_rep);

Any help would be greatly appreciated.

Thanks Received on Tue Jan 21 2003 - 11:12:59 CST