Your actual SQL is not really the same type of query as your theorized
SQL.
In your actual SQL, you have one extra dimension in the cube :
application_date, which is not included in your select list. I'd bet
the variation in the totals in question is due to the variation in
application_date. If you include application_date in the select list,
the results should look nicer.
lj485_at_hotmail.com (jsicore) wrote in message news:<fbc39b4a.0301210912.4214579e_at_posting.google.com>...
> 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 Wed Jan 22 2003 - 00:55:13 CST
