oracle10g，一个聚合查询难题。。。

Question

直入正题： 表mail_in :
其中mail_num列有重复，代表邮件编号；org_code 也有重复，代表投递机构；dlv_sts_code 也有重复，代表投递状态。
业务简介：投递员投递一次邮件，就会在该表中产生一条记录投递邮件号，投递员所属机构，投递状态的信息。投递状态可能为H也可能为I。
要求：用一个查询语句 查出每个机构投递邮件总数和投递状态为I的数量。（一个邮件编号代表一个邮件）。
在线等

Answer 1

SELECT 
       ORG_CODE,NUMI+NUMH NUMALL,NUMI
       FROM
       (
        select 
              org_code,sum(decode(dlv_sts_code,'I',1,0)NUMI,sum(decode(dlv_sts_code,'H',1,0)NUMH
        from 
            (
              select distinct mail_num,org_code,dlv_sts_code)ta
        GROUP BY ta.org_code,ta.dlv_sts_code
        )

Answer 2

这样？
with t1 as
(
     select '02046' c1,'26636200' c2,'H' c3 from dual
     union all
     select '02046' c1,'26636200' c2,'H' c3 from dual
     union all
     select '02046' c1,'26636200' c2,'H' c3 from dual
     union all
     select '02046' c1,'26636200' c2,'H' c3 from dual
     union all
     select '02049' c1,'26636200' c2,'I' c3 from dual
     union all
     select '02147' c1,'26636400' c2,'I' c3 from dual
     union all
     select '02160' c1,'26636101' c2,'I' c3 from dual
     union all
     select '02160' c1,'26636101' c2,'H' c3 from dual
     union all
     select '02245' c1,'26636400' c2,'I' c3 from dual
     union all
     select '02315' c1,'26636200' c2,'I' c3 from dual
)select c1 机构,count(c2) 邮件总数,sum(decode(c3,'I',1,0)) 状态为I的数量
from t1
group by c1
order by c1     机构  邮件总数 状态为I的数量
---------------------------------
1 02046 4 0
2 02049 1 1
3 02147 1 1
4 02160 2 1
5 02245 1 1
6 02315 1 1

Answer 3

简单做了几笔数据模拟
WITH t AS(
SELECT '02046'mail_num,'26636200'org_code,'H'dlv_sts_code FROM dual
UNION ALL 
SELECT '02046','26636200','H' FROM dual
UNION ALL 
SELECT '02046','26636200','I' FROM dual
UNION ALL 
SELECT '02047','26636201','I' FROM dual
UNION ALL 
SELECT '02047','26636201','I' FROM dual
UNION ALL 
SELECT '02047','26636202','I' FROM dual
)
SELECT DISTINCT 
org_code,
Count(*) over (PARTITION BY org_code ),
Count(Decode(dlv_sts_code,'I',1,NULL)) over (PARTITION BY org_code )
 FROM t