create table test
( dept varchar2(20) not null,
count0 number(5,0) null,
count1 number(5,0) null,
count2 number(5,0) null
);
insert into test
(dept)
select distinct dept from tmp;update test a set a.count0=
(select count(*) from tmp b where a.dept=b.dept and b.type=0);update test a set a.count1=
(select count(*) from tmp b where a.dept=b.dept and b.type=1);update test a set a.count2=
(select count(*) from tmp b where a.dept=b.dept and b.type=2);select * from test;
( dept varchar2(20) not null,
count0 number(5,0) null,
count1 number(5,0) null,
count2 number(5,0) null
);
insert into test
(dept)
select distinct dept from tmp;update test a set a.count0=
(select count(*) from tmp b where a.dept=b.dept and b.type=0);update test a set a.count1=
(select count(*) from tmp b where a.dept=b.dept and b.type=1);update test a set a.count2=
(select count(*) from tmp b where a.dept=b.dept and b.type=2);select * from test;
max((select count(*) from tmp t2 where t1.dept=t2.dept and type='0')) c0,
max((select count(*) from tmp t2 where t1.dept=t2.dept and type='1')) c1,
max((select count(*) from tmp t2 where t1.dept=t2.dept and type='2')) c2
from tmp t1
group by dept
select
dept,
sum(DECODE(TYPE,'0',1,0)),
sum(DECODE(TYPE,'1',1,0)),
sum(DECODE(type,'2',1,0))
from tt
group by dept;
a.dept dept,
sum(decode(a.type,0,1,0)) count(0),
sum(decode(a.type,1,1,0)) count(1),
sum(decode(a.type,2,1,0)) count(2)
from
tmp a
group by
a.dept