select t.區,count(*) 總數,sum(case t.屬性值 when 0 then 1 when 1 then 0 end) 屬性值為0的總數 from( select 'S' as 區,1 as 號碼,0 as 屬性值 from dual union all select 'F',2,1 from dual union all select 'F',3,0 from dual union all select 'S',4,1 from dual union all select 'D',5,0 from dual union all select 'D',6,1 from dual union all select 'D',6,0 from dual union all select 'W',6,0 from dual union all select 'W',6,0 from dual union all select 'T',6,0 from dual union all select 'D',7,0 from dual ) t group by t.區----------------------------------- 區 總數 屬性值為0的總數 1 D 4 3 2 F 2 1 3 S 2 1 4 T 1 1 5 W 2 2
select 营业区,count(*),(count(*)-sum(属性值)), (count(*)-sum(属性值))/count(*) from bl_rkyk group by 营业区
select t.用户业务号码 as 营业区, count(*) 总用户数, sum(case t.属性值 when 0 then 1 when 1 then 0 end) 属性为0的用户, --属性值为0则显示1,1则显示0,然后sum就得出属性为0的用户数 to_number(to_char(sum(case t.属性值 when 0 then 1 when 1 then 0 end)/count(*),'FM999999999999999999.0000000000000')) as 转换率 from bl_rkyk as t group by t.用户业务号码
看錯了再來select t.营业区 as 营业区, count(*) 总用户数, sum(case t.属性值 when 0 then 1 when 1 then 0 end) 属性为0的用户, --属性值为0则显示1,1则显示0,然后sum就得出属性为0的用户数 to_number(to_char(sum(case t.属性值 when 0 then 1 when 1 then 0 end)/count(*),'FM999999999999999999.0000000000000')) as 转换率 from bl_rkyk as t group by t.营业区
select t.區,count(*) 總數,sum(case t.屬性值 when 0 then 1 when 1 then 0 end) 屬性值為0的總數
from(
select 'S' as 區,1 as 號碼,0 as 屬性值 from dual union all
select 'F',2,1 from dual union all
select 'F',3,0 from dual union all
select 'S',4,1 from dual union all
select 'D',5,0 from dual union all
select 'D',6,1 from dual union all
select 'D',6,0 from dual union all
select 'W',6,0 from dual union all
select 'W',6,0 from dual union all
select 'T',6,0 from dual union all
select 'D',7,0 from dual
) t
group by t.區-----------------------------------
區 總數 屬性值為0的總數
1 D 4 3
2 F 2 1
3 S 2 1
4 T 1 1
5 W 2 2
3525198 榆林 1
7703286 横山 1
4856653 靖边 0
4751725 靖边 0
8589660 神木 1
8739519 府谷 1
3467771 榆林 1
5883802 绥德 1
3218581 榆林 1
yl5622561 绥德 1要求统计结果为:
营业区 总用户数 属性为0的用户 转换率(属性为0的用户/总用户)
定边 64854 37086 57.1838282912388
靖边 77111 43606 56.5496492069873
清涧 36102 19730 54.6507118719185
吴堡 20277 8416 41.5051536223307
from bl_rkyk
group by 营业区
select
t.用户业务号码 as 营业区,
count(*) 总用户数,
sum(case t.属性值 when 0 then 1 when 1 then 0 end) 属性为0的用户,
--属性值为0则显示1,1则显示0,然后sum就得出属性为0的用户数
to_number(to_char(sum(case t.属性值 when 0 then 1 when 1 then 0 end)/count(*),'FM999999999999999999.0000000000000')) as 转换率
from bl_rkyk as t
group by t.用户业务号码
t.营业区 as 营业区,
count(*) 总用户数,
sum(case t.属性值 when 0 then 1 when 1 then 0 end) 属性为0的用户,
--属性值为0则显示1,1则显示0,然后sum就得出属性为0的用户数
to_number(to_char(sum(case t.属性值 when 0 then 1 when 1 then 0 end)/count(*),'FM999999999999999999.0000000000000')) as 转换率
from bl_rkyk as t
group by t.营业区