select to_char(to_date(systemtime,'yyyy-mm-dd hh24:mi:ss'),'yyyymmdd') as datetime,
sum(case when responsecode='00'and money>0 then 1 else 0 end)  
  successcount,
sum(case when responsecode<>'00' and money<=0 then 1 else 0 end)
  failercount,
sum(case when responsecode='98' then 1 else 0 end)
  yinlianerror
from transline
group by to_char(to_date(systemtime,'yyyy-mm-dd hh24:mi:ss'),'yyyymmdd')
order by to_char(to_date(systemtime,'yyyy-mm-dd hh24:mi:ss'),'yyyymmdd')

------解决方案--------------------

SQL code

create table t1 (SYSTEMTRACE number(5),responsecode varchar2(10),t_time varchar2(20),money number(10,2));

insert into t1 values (1,'00','20120101121212',100);
insert into t1 values (2,'98','20120101121212',200);
insert into t1 values (3,'00','20120101121212',300);
insert into t1 values (4,'11','20120101121212',150);
insert into t1 values (5,'98','20120101121212',250);
insert into t1 values (6,'00','20120102121212',500);
insert into t1 values (7,'11','20120102121212',200);
insert into t1 values (8,'00','20120103121212',300); 
insert into t1 values (9,'00','20120103121212',100);
insert into t1 values (10,'11','20120103121212',700);
commit;


select to_date(substr(t_time,1,8),'yyyy-mm-dd') time,
       sum(decode(responsecode,'00',1,0)) c1,
       sum(decode(responsecode,'11',1,0)) c2,
       sum(decode(responsecode,'98',1,0)) c3
from t1
group by substr(t_time,1,8)
order by substr(t_time,1,8)



       time      c1    c2    c3
--------------------------------------------
1    2012/1/1    2    1    2
2    2012/1/2    1    1    0
3    2012/1/3    2    1    0