这样的统计该如何实现

[dongyuan234]

编号 区域 类型
1 二区 商用
2 二区 民用
3 三区 商用
4 三区 民用
4 三区 民用 （注释此行是重复数据）

实现下面的统计形式 SQL该怎么写（排除重复数据）

区域 商用总数 民用总数
二区 1 1
三区 1 1

[iqlife]

select 区域,sum(商用) as 商用总数,sum(民用) as 民用总数
from (
select 区域 ,类型 ,decode(类型,'商用',1,0) as 商用,decode(类型,'民用',1,0) as 民用
from tab
)
group by 区域

[micky_ya]

select 区域,sum(case when 类型='商用' then 1 else 0 end) "商用总数",sum(case when 类型='民用' then 1 else 0 end) "民用总数" from table group by 区域

[siakang]

select 区域,sum(商用) as 商用总数,sum(民用) as 民用总数
from (
select 区域 ,类型 ,decode(类型,'商用',1,0) as 商用,decode(类型,'民用',1,0) as 民用
from (select distinct * from table  )
)
group by 区域

[爱吃芒果的猴子]

select 区域,sum(case when 类型='商用' then 1 else 0 end) "商用总数",sum(case when 类型='民用' then 1 else 0 end) "民用总数" from table group by 区域 

[dongyuan234]

编号 区域 类型 费用
1 二区 商用 200
2 二区 民用 100
3 三区 商用 300
4 三区 民用 100
4 三区 民用

实现下面的统计形式 SQL该怎么写（排除编号重复数据）

区域 商用总数 民用总数 费用合计
二区 1 1 300
三区 1 1 400


改成这样语句怎么写

[ojuju10]

with t as
(
select 1 as id,'二区' as name ,'商用' as type from dual
union all
select 2 as id,'二区' as name ,'民用' as type from dual
union all
select 3 as id,'三区' as name ,'商用' as type from dual
union all
select 4 as id,'三区' as name ,'民用' as type from dual
union all
select 4 as id,'三区' as name ,'民用' as type from dual
)
select name,sum(case when type='商用' then 1 else 0 end),
sum(case when type='民用' then 1 else 0 end) from
(select distinct name,type from t)
group by name;

[dingjun123]

select area, count(decode(way, '商用', way)) 商用, count(decode(way, '民用', way)) 民用, sum(fee) 总费用
  from (select id, area, way, fee
           from t
          where t.rowid =
                (select max(tt.rowid)
                   from t tt
                  where tt.id = t.id and tt.fee is not null))
 group by area;

[ojuju10]

with t as
(
select 1 as id,'二区' as name ,'商用' as type,200 as cast from dual
union all
select 2 as id,'二区' as name ,'民用' as type,100 as cast from dual
union all
select 3 as id,'三区' as name ,'商用' as type,300 as cast from dual
union all
select 4 as id,'三区' as name ,'民用' as type,100 as cast from dual
union all
select 4 as id,'三区' as name ,'民用' as type,0 as cast from dual
)

select name,sum(case when type='商用' then 1 else 0 end),
sum(case when type='民用' then 1 else 0 end),sum(cast) from
(select * from t a where not exists(select 1 from t b where a.id=b.id and a.cast<b.cast )) t
group by name;

[qq82296344]

select 区域,sum(商用) as 商用总数,sum(民用) as 民用总数
from (
select 区域 ,类型 ,decode(类型,'商用',1,0) as 商用,decode(类型,'民用',1,0) as 民用
from (select distinct * from table  )
)
group by 区域

[欢乐极客]

高手如云啊

[kejian1986]

我验证了大家的方法，都基本正确，再提一问，如果有一区 二区 三区 这时候统计的结果顺序是二区 三区 一区,我猜想应该是根据erqu sanqu yiqu顺序显示的 如何更改可以实现一区 二区 三区的排列顺序。

[minoboy]

sum 也可以用ＣＯＵＮＴ来实现．

[Phoenix_99]

with t as
(
select 1 as id,'二区' as name ,'商用' as type,200 as cast from dual
union all
select 2 as id,'二区' as name ,'民用' as type,100 as cast from dual
union all
select 3 as id,'三区' as name ,'商用' as type,300 as cast from dual
union all
select 4 as id,'三区' as name ,'民用' as type,100 as cast from dual
union all
select 4 as id,'三区' as name ,'民用' as type,0 as cast from dual
)

select name,sum(case when type='商用' then 1 else 0 end) 商用总数,
sum(case when type='民用' then 1 else 0 end) 民用总数,sum(cast) 费用合计 from  
(select * from t a where not exists(select 1 from t b where a.id=b.id and a.cast<b.cast )) t
group by name;

[xiaosheng2008]

select
name,
count(distinct case when type='商用' then 1 end ) as 商用总数,
count(distinct case when type='民用' then 1 end ) as 民用总数
from table_name
group by name;