oracle 中按照时间分组统计

    ASK_TIME      ATM_NUMBER
   2012-01-01        2
   2012-02-01        4
   2012-05-01        6
   2012-08-01        8
1.按年份分组
 
   SELECT (to_char(ASK_TIME,'yyyy')) as 年份, (sum(ATM_NUMBER)) as ATM数量
   FROM TB_KFWH_WORKREPORT GROUP BY to_char(ASK_TIME,'yyyy')
--------------------------------------------------------------------
   年份    ATM数量
   2012     20
2.按月份分组

   SELECT (to_char(ASK_TIME,'yyyy-mm')) as 月份, (sum(ATM_NUMBER)) as ATM数量
   FROM TB_KFWH_WORKREPORT GROUP BY to_char(ASK_TIME,'yyyy-mm')
  -----------------------------------------------------------------------
    月份     ATM数量
    2012-01    2
    2012-02    4
    2012-05    6
    2012-08    8
3.按星期分组
 
   SELECT (to_char(ASK_TIME,'yyyy-IW')) as 星期, (sum(ATM_NUMBER)) as ATM数量
   FROM TB_KFWH_WORKREPORT GROUP BY to_char(ASK_TIME,'yyyy-IW')

4.按季度分组

   SELECT (to_char(ASK_TIME,'yyyy-Q')) as 季度, (sum(ATM_NUMBER)) as ATM数量
   FROM TB_KFWH_WORKREPORT GROUP BY to_char(ASK_TIME,'yyyy-Q')
-----------------------------------------------------------------
     季度    ATM数量
    2012-1     6
    2012-2     6
    2012-3     8
或者
 
  SELECT (to_char(ASK_TIME,'yyyy')) as 年份 ,
sum(decode(to_char(ASK_TIME,'mm'),'01',ATM_NUMBER,'02',ATM_NUMBER,'03',ATM_NUMBER,'0')) as 第一季,
sum(decode(to_char(ASK_TIME,'mm'),'04',ATM_NUMBER,'05',ATM_NUMBER,'06',ATM_NUMBER,'0')) as 第二季,
sum(decode(to_char(ASK_TIME,'mm'),'07',ATM_NUMBER,'08',ATM_NUMBER,'09',ATM_NUMBER,'0')) as 第三季,
sum(decode(to_char(ASK_TIME,'mm'),'10',ATM_NUMBER,'11',ATM_NUMBER,'12',ATM_NUMBER,'0')) as 第四季
FROM TB_KFWH_WORKREPORT GROUP BY to_char(ASK_TIME,'yyyy')
------------------------------------------------------------
  年份  第一季 第二季 第三季 第四季
  2012     6      6       8      0