急求：统计数据（一解决即结贴）

[liangyubiao]

如何统计出数据：
业务表 T_BUSINESS里有数据： POSITION中12代表出证，03未出证
DEPARTID POSITION ID
007514637 12 080131002
00751440x 03 080131003
00751440x 12 080131004
00751440x 12 080131001

部门T_DEPARTMENT 里有：
ID DEPARTNAME
007514637 文管局
00751440x 交通局
现在想通过业务表里统计出数据：
部门名称，所有业务，已出证业务
请问怎么实现啊？？各位。


SELECT D.NAME, COUNT(*) AS 受理业务,(SELECT COUNT(*) FROM T_BUSINESS INNER JOIN T_DEPARTMENT ON
T_BUSINESS.DEPARTID=T_DEPARTMENT.ID WHERE LASTPOSITIONID=12) AS 出证
FROM T_BUSINESS B INNER JOIN T_DEPARTMENT D ON B.DEPARTID=D.ID
GROUP BY D.ID,D.NAME
这里统计出来的结果有
部门名称 受理业务 出证
文管局 3 3
交通局 1 3
结果是出证的统计了所有的。我要让它每个局只统计本局的。就像受理业务那样。。请各位帮帮忙。太感谢了

[pt1314917]

select a.departname 部门名称,b.cnts 受理业务,c.cnt 出证
from T_DEPARTMENT a,(select departid,count(1)cnts from T_BUSINESS group by DEPARTID)b,
(select departid,cnt=sum(case position when '12' then 1 else 0 end) from T_BUSINESS)c
where a.ID=b.departid and a.id=c.departid

[冷箫轻笛]

select b.DEPARTNAME ,count(1) as 受理业务,
       sum(case POSITION when '12' then 1 else 0 end) as 出证
from T_BUSINESS a inner join T_DEPARTMENT b on a.DEPARTID = b.ID
group by b.ID,b.DEPARTNAME 

[谁是谁的谁]

declare @T_Business table (DepartId varchar(9),Position varchar(2),Id varchar(9))
declare @T_Department table(Id varchar(9),Departname varchar(6))
insert into @T_Business 
select '007514637', '12', '080131002' union all 
select '00751440x', '03', '080131003' union all 
select '00751440x', '12', '080131004' union all 
select '00751440x', '12', '080131001' 

insert into @T_Department 
select '007514637',         '文管局' union all 
select '00751440x',         '交通局'

select * from @T_Business
select * from @T_Department

select a.Departname,b.受理业务,c.出证
from @T_Department a,
(select departid ,count(position) as 受理业务 from @T_Business group by departid) b,
(select departid ,count(position) as 出证 from @T_Business where position ='12' group by departid)c
where a.id=b.departid and b.departid*=c.departid
/*
（所影响的行数为 4 行）


（所影响的行数为 2 行）

DepartId  Position Id        
--------- -------- --------- 
007514637 12       080131002
00751440x 03       080131003
00751440x 12       080131004
00751440x 12       080131001

（所影响的行数为 4 行）

Id        Departname 
--------- ---------- 
007514637 文管局
00751440x 交通局

（所影响的行数为 2 行）

Departname 受理业务        出证          
---------- ----------- ----------- 
交通局        3           2
文管局        1           1

（所影响的行数为 2 行）
*/

[-狙击手-]

declare @T_Business table (DepartId varchar(9),Position varchar(2),Id varchar(9))
declare @T_Department table(Id varchar(9),Departname varchar(6))
insert into @T_Business 
select '007514637', '12', '080131002' union all 
select '00751440x', '03', '080131003' union all 
select '00751440x', '12', '080131004' union all 
select '00751440x', '12', '080131001' 

insert into @T_Department 
select '007514637',         '文管局' union all 
select '00751440x',         '交通局'


select b.DEPARTNAME as 部门名称,count(1) as 受理业务,
       sum(case POSITION when '12' then 1 else 0 end) as 出证
from @T_BUSINESS a 
right join @T_DEPARTMENT b on a.DEPARTID = b.ID
group by b.ID,b.DEPARTNAME

/*
部门名称   受理业务        出证          
------ ----------- ----------- 
交通局    3           2
文管局    1           1

（所影响的行数为 2 行）
*/

[victorcai2006]

依然统计问题，COUNT+GROUP BY

select b.DEPARTNAME ,count(1) as 受理业务,
       sum(case POSITION when '12' then 1 else 0 end) as 出证
from T_BUSINESS a inner join T_DEPARTMENT b on a.DEPARTID = b.ID
group by b.ID,b.DEPARTNAME 

可以结贴了。。。。

[dawugui]

让它每个局只统计本局的

SELECT D.NAME, COUNT(*) AS 受理业务,(SELECT COUNT(*) FROM T_BUSINESS INNER JOIN T_DEPARTMENT ON 
 T_BUSINESS.DEPARTID=T_DEPARTMENT.ID WHERE LASTPOSITIONID=12 and DEPARTNAME = '某个局') AS 出证 
 FROM T_BUSINESS B INNER JOIN T_DEPARTMENT D ON B.DEPARTID=D.ID and DEPARTNAME = '某个局'
GROUP BY D.ID,D.NAME 

[pt1314917]

declare @T_Business table (DepartId varchar(9),Position varchar(2),Id varchar(9))
declare @T_Department table(Id varchar(9),Departname varchar(6))
insert into @T_Business 
select '007514637', '12', '080131002' union all 
select '00751440x', '03', '080131003' union all 
select '00751440x', '12', '080131004' union all 
select '00751440x', '12', '080131001' 

insert into @T_Department 
select '007514637',         '文管局' union all 
select '00751440x',         '交通局'


select a.departname 部门名称,b.cnts 受理业务,c.cnt 出证
from @T_Department a,(select departid,count(1)cnts from @T_Business group by DEPARTID)b,
(select departid,cnt=count(1) from @T_Business where position='12' group by DEPARTID)c
where a.ID=b.departid and a.id=c.departid

[zefuzhang2008]

select a.departname 部门名称,b.cnts 受理业务,c.cnt 出证
from T_Department a,(select departid,count(1)cnts from T_Business group by DEPARTID)b,
(select departid,cnt=count(1) from T_Business where position='12' group by DEPARTID)c
where a.ID=b.departid and a.id=c.departid