Oracle 10g : Monthly stats with grouping by file size

Question

I am on Oracle 10g. I have a table that contains all the files stored in the system during the past year. I want to make statistical monthly deposits, grouping them by file size. eg

0-1m
1m-10m
10m-100m
100m +

So my results would look like :

Month, 0-1m, 1m-10m, 10m-100m, 100mplus
2009-03, 999, 999, 999, 999

I want to use Oracle's analytic functions but they are not familiar to me.

Your help would be greatly appreciated.

update: I have this query which gives me a flat list.

select mois, groupement, count(*) nb
from 
  (
  select to_char(vercdate,'YYYY-MM') mois, 
          case 
            when datasize > 1024*1024*1024 then 'Go'
            when datasize > 1024*1024*100 then '100Mo'
            when datasize > 1024*1024*10 then '10Mo'
            when datasize > 1024*1024 then '1Mo'
            else '0Mo'
          end groupement
    from table
    where lower(filetype) = 'pdf'
  )
where groupement <> '0Mo'
GROUP by mois, groupement

which gives something like :

2007-08 1Mo 745
2007-08 10Mo 48
2007-09 1Mo 973
2007-09 10Mo 175
2007-09 100Mo 1
2007-10 1Mo 913
2007-10 10Mo 64
2007-11 1Mo 828
2007-11 10Mo 71
2007-12 1Mo 456
2007-12 10Mo 24
2007-12 100Mo 1
2008-01 1Mo 693

Now I have to make the analytical part.

Answer 1

this is too much work to do if from scratch. those key words can help you Decode, Sum Over (Partition By).

Answer 2

I don't see the analytical part - it just looks like you need to pivot the data, like so:

WITH example AS (
     SELECT EXTRACT(YEAR FROM t.vercdate) 'y',
            EXTRACT(MONTH FROM t.vercdate) 'm',
            CASE
              WHEN t.datasize > 1024*1024*1024 THEN 'Go'
              WHEN t.datasize > 1024*1024*100 THEN '100Mo'
              WHEN t.datasize > 1024*1024*10 THEN '10Mo'
              WHEN t.datasize > 1024*1024 THEN '1Mo'
              ELSE '0Mo'
            END groupement
       FROM TABLE t
      WHERE LOWER(t.filetype) = 'pdf')
SELECT t.mois,
       CASE WHEN t.groupement = '1Mo' THEN t.nb ELSE NULL END '0-1m',
       CASE WHEN t.groupement = '10Mo' THEN t.nb ELSE NULL END '1m-10m',
       CASE WHEN t.groupement = '100Mo' THEN t.nb ELSE NULL END '10m-100m',
       ...etc...
  FROM (SELECT e.y || '-' || e.m mois, 
               groupement, 
               COUNT(e.*) 'nb'
          FROM example e
         WHERE e.groupement != '0Mo'
      GROUP BY e.y, e.m, e.groupement) t

Reference: EXTRACT