tough SELECT statement (Absolute Deviation)...

Hello all,

I am developing a simple tool for generating the required DDL and DML for building and maintaining analytic tables to achieve Data Mining. The automatically generated tables will contain lot of columns as result of applying
several different aggregation functions on each different grouping criteria e.g.
(Account_ID, Person_ID, Month).

The problem is that the SQL statements that feed those tables should be as simple and self contained as possible e.g. would be much prefered that the SELECT query which generates each column does not imply introducing special clauses in the global FROM or WHERE clauses, so perhaps Analytic functions would be a very good candidate.

The concrete problem I currently have is that one of the columns required is the not natively supported "Absolute Deviation" i.e. sum(abs(amount) - avg(amount)) and this statement is not accepted because it has nested aggregations sum(...avg()).

**INCORRECT** SELECT
ACCOUNT_ID,
to_char(transactiondate,'YYYYMM') AS MONTH,
(sum(abs(amount - avg(amount)))) AS ABSDEV_AMOUNT,
(max(transactiondate) - min(transactiondate)) AS INTERVAL_DATE
FROM transactions
GROUP BY ACCOUNT_ID, to_char(transactiondate,'YYYYMM');

**CORRECT** SELECT
t1.ACCOUNT_ID,
to_char(t1.transactiondate,'YYYYMM') AS MONTH,
(sum(abs(t1.amount - t2.AVERAGE_AMOUNT))) AS ABSDEV_AMOUNT,
(max(t1.transactiondate) - min(t1.transactiondate)) AS INTERVAL_DATE
FROM transactions t1, ( SELECT

   ACCOUNT_ID,
   to_char(transactiondate,'YYYYMM') AS MONTH,    avg(amount) AS AVERAGE_AMOUNT
   FROM transactions
   GROUP BY ACCOUNT_ID, to_char(transactiondate,'YYYYMM')) t2 GROUP BY t1.ACCOUNT_ID, to_char(t1.transactiondate,'YYYYMM');

Any suggestions?

TIA,
Best regards,
Giovanni Received on Tue Jan 14 2003 - 05:03:50 CST