well, we cannot do the column headings like that unless you want to do dynamic sql (then I can write a procedure that returns this result set exactly).

In straight sql, one approach is:

ops$tkyte@ORA817DEV.US.ORACLE.COM> select object_type,
  2         max( decode( rn, 1, sum_oid, null ) ) month1,
  3         max( decode( rn, 2, sum_oid, null ) ) month2,
  4         max( decode( rn, 1, sum_oid, null ) ) +
  5         max( decode( rn, 2, sum_oid, null ) ) summed,
  6         min( monthYear ),
  7         max( monthYear )
  8  from (
  9  select object_type,
 10         monthYear,
 11         row_number() over ( partition by object_type
 12                             order by monthYear ) rn,
 13         sum_oid
 14    from (
 15  select object_type, trunc(created,'mm') MonthYear, sum(object_id) sum_oid
 16    from all_objects
 17   where created >= ( select trunc( add_months( max(created),-1 ), 'mm' )
 18                                            from all_objects
 19                                           where object_type = 'TABLE' )
 20     and object_type = 'TABLE'
 21   group by object_type, trunc(created,'mm')
 22     )
 23  )
 24  group by object_type
 25  /

OBJECT_TYPE            MONTH1     MONTH2     SUMMED MIN(MONTH MAX(MONTH
------------------ ---------- ---------- ---------- --------- ---------
TABLE                  225648      57131     282779 01-SEP-01 01-OCT-01


 

well, your output is a little funky.  

for example, why is there a procedure with amount = 0 but no table with amount = 0?

why is there a table with a prevmonthamount = 0, yet no procedure with the same?  

I mean -- according to your rules, there would be both for both?


But in any case -- here is one that shows the amounts by month and if there is a prior months amount -- that is included, else it is zero:

ops$tkyte@ORA920> select object_type, month, amount,
  2         decode( add_months(month,-1), last_month, last_amt, 0 ) last_month
  3    from (
  4  select object_type, month, amount,
  5         lag(amount) over (partition by object_type order by month ) last_amt,
  6         lag(month) over (partition by object_type order by month ) last_month
  7    from (
  8  select object_type, month, sum(amount) amount
  9    from t
 10   group by object_type, month
 11         )
 12         )
 13  /

OBJECT_TYPE        MONTH         AMOUNT LAST_MONTH
------------------ --------- ---------- ----------
PROCEDURE          01-MAY-02     505564          0
PROCEDURE          01-SEP-02      31158          0
PROCEDURE          01-OCT-02      31886      31158
PROCEDURE          01-DEC-02      34561          0
PROCEDURE          01-JAN-03     221431      34561
TABLE              01-MAY-02   11727977          0
TABLE              01-AUG-02     182975          0
TABLE              01-OCT-02      32177          0
TABLE              01-NOV-02      34031      32177
TABLE              01-DEC-02     104999      34031
TABLE              01-JAN-03    2377920     104999
TABLE              01-FEB-03   38537263    2377920

12 rows selected.

Now, if you REALLY need those extra rows (not clear) with amount=0 and prevmonthamounts -- then we can cartesian product with a 2 row table (we need to output that last row for each object type twice see) and do a little decodeing:

ops$tkyte@ORA920> select object_type,
  2         decode( xx, 1, month, add_months(month,1)) month,
  3             decode( xx, 1, amount, 0 ) amount,
  4             decode( xx, 1, last_month, amount) last_month,
  5             xx
  6    from (
  7  select object_type, month, amount,
  8         decode( add_months(month,-1), last_month, last_amt, 0 ) last_month,
  9             max(month) over ( partition by object_type ) max_month,
 10             xx
 11    from (
 12  select object_type, month, amount,
 13         lag(amount) over (partition by object_type order by month ) last_amt,
 14         lag(month) over (partition by object_type order by month ) last_month
 15    from (
 16  select object_type, month, sum(amount) amount
 17    from t
 18   group by object_type, month
 19         )
 20         ), (select 1 xx from dual union all select 2 xx from dual )
 21         )
 22   where xx = 1 OR ( xx = 2 and month = max_month )
 23   order by 1,2
 24  /

OBJECT_TYPE        MONTH         AMOUNT LAST_MONTH         XX
------------------ --------- ---------- ---------- ----------
PROCEDURE          01-MAY-02     505564          0          1
PROCEDURE          01-SEP-02      31158          0          1
PROCEDURE          01-OCT-02      31886      31158          1
PROCEDURE          01-DEC-02      34561          0          1
PROCEDURE          01-JAN-03     221431      34561          1
PROCEDURE          01-FEB-03          0     221431          2
TABLE              01-MAY-02   11727977          0          1
TABLE              01-AUG-02     182975          0          1
TABLE              01-OCT-02      32177          0          1
TABLE              01-NOV-02      34031      32177          1
TABLE              01-DEC-02     104999      34031          1
TABLE              01-JAN-03    2377920     104999          1
TABLE              01-FEB-03   38537263    2377920          1
TABLE              01-MAR-03          0   38537263          2

14 rows selected.



there you are.