图(1):
A B C
a 1 2
a 0 3
b 1 1
b 0 1
c 1 1
图(1)中C是count(*)出来的,理想的话应该是不按B分组的,而是只按A分组:
图(2):
A E F
a 40% 60%
b 50% 50%
c 100% 0
即图(1)中B是1的占A是a的总记录数的百分比,即图(2)中的E:2/(2+3)=0.4
B是0的占A是a的总记录数的百分比,即图(2)中的F:3/(2+3)=0.6
请问有什么方法在统计的时候再进行计算得出百分比E、F,如图(2)所示。
大家明白我的意思吧,能不能写个语句出来,我不想得到图(1)的结果后再在程序
中计算,而是想直接得到图(2)的最终结果,谢谢大家了!!!!!
11 个解决方案
#1
select x.a,convert(numeric(10,2),x.c)/(x.c+y.c) as e,convert(numeric(10,2),y.c)/(x.c+y.c) as f
from [图(1)] x,[图(1)] y
where x.a=y.a
and x.b=1
and y.b=0
group by x.a
这是个思路
from [图(1)] x,[图(1)] y
where x.a=y.a
and x.b=1
and y.b=0
group by x.a
这是个思路
#2
--如果从上面的结果来统计,就是这样
select a
,E=cast(cast(sum(case b when 1 then c*100.0 else 0 end)
/sum(c) as decimal(10,2)) as varchar)+'%'
,F=cast(cast(sum(case b when 0 then c*100.0 else 0 end)
/sum(c) as decimal(10,2)) as varchar)+'%'
from [图(1)]
group by a
select a
,E=cast(cast(sum(case b when 1 then c*100.0 else 0 end)
/sum(c) as decimal(10,2)) as varchar)+'%'
,F=cast(cast(sum(case b when 0 then c*100.0 else 0 end)
/sum(c) as decimal(10,2)) as varchar)+'%'
from [图(1)]
group by a
#3
--如果你要直接出结果,就将你原来的语句帖出来
#4
如果[图(1)]是个汇总语句,如:
select a,b,count(*) from tablename
group by a,b
可以直接改为:
select a,sum(case when b=1 then 1.0 else 0.0 end)/sum(1.0),
sum(case when b=0 then 1.0 else 0 end)/sum(1.0)
from tablename
group by a
select a,b,count(*) from tablename
group by a,b
可以直接改为:
select a,sum(case when b=1 then 1.0 else 0.0 end)/sum(1.0),
sum(case when b=0 then 1.0 else 0 end)/sum(1.0)
from tablename
group by a
#5
原来的语句(图1的结果):
select a.co_name,c.if_app,count(*)
from AGENT_CO a,AGENT_CO_YEAR b,AGENT_CLIENT_DATA c
where a.level_no = b.level_no and a.co_no = b.co_no
and a.level_no = c.level_no and a.co_no = c.co_no
where a.level_no = '440601' and c.year_no = '20034'
group by a.co_name,c.if_app
select a.co_name,c.if_app,count(*)
from AGENT_CO a,AGENT_CO_YEAR b,AGENT_CLIENT_DATA c
where a.level_no = b.level_no and a.co_no = b.co_no
and a.level_no = c.level_no and a.co_no = c.co_no
where a.level_no = '440601' and c.year_no = '20034'
group by a.co_name,c.if_app
#6
楼主搞错了吧? 你那个语句根本就执行不了.
#7
--应该是这样写的才对:
select a.co_name
,E=cast(cast(sum(case c.if_app when 1 then 100.0 else 0.0 end)
/count(*) as decimal(10,2)) as varchar)+'%'
,F=cast(cast(sum(case c.if_app when 0 then 100.0 else 0.0 end)
/count(*) as decimal(10,2)) as varchar)+'%'
from AGENT_CO a
join AGENT_CO_YEAR b on a.level_no = b.level_no and a.co_no = b.co_no
join AGENT_CLIENT_DATA c on a.level_no = c.level_no and a.co_no = c.co_no
where a.level_no = '440601' and c.year_no = '20034'
group by a.co_name
select a.co_name
,E=cast(cast(sum(case c.if_app when 1 then 100.0 else 0.0 end)
/count(*) as decimal(10,2)) as varchar)+'%'
,F=cast(cast(sum(case c.if_app when 0 then 100.0 else 0.0 end)
/count(*) as decimal(10,2)) as varchar)+'%'
from AGENT_CO a
join AGENT_CO_YEAR b on a.level_no = b.level_no and a.co_no = b.co_no
join AGENT_CLIENT_DATA c on a.level_no = c.level_no and a.co_no = c.co_no
where a.level_no = '440601' and c.year_no = '20034'
group by a.co_name
#8
不会吧,有问题吗?我用的是sybase,没问题的,sqlserver同样也可以运行
我现在不在公司,不过大概就是这样子,不好意思上面语句搞错了:
select a.co_name,c.if_app,count(*)
from AGENT_CO a,AGENT_CO_YEAR b,AGENT_CLIENT_DATA c
where a.level_no = b.level_no and a.co_no = b.co_no
and a.level_no = c.level_no and a.co_no = c.co_no
and a.level_no = '440601' and b.year_no = '20034'
group by a.co_name,c.if_app
我现在不在公司,不过大概就是这样子,不好意思上面语句搞错了:
select a.co_name,c.if_app,count(*)
from AGENT_CO a,AGENT_CO_YEAR b,AGENT_CLIENT_DATA c
where a.level_no = b.level_no and a.co_no = b.co_no
and a.level_no = c.level_no and a.co_no = c.co_no
and a.level_no = '440601' and b.year_no = '20034'
group by a.co_name,c.if_app
#9
我大概明白你的意思,应该是这样,非常感谢
#10
你仔细看看你第一次帖出来的语句,居然有两个where,后面这个没问题了.
#11
结帖,明天早上回公司搞定它,有问题我会再来麻烦你,呵呵
#1
select x.a,convert(numeric(10,2),x.c)/(x.c+y.c) as e,convert(numeric(10,2),y.c)/(x.c+y.c) as f
from [图(1)] x,[图(1)] y
where x.a=y.a
and x.b=1
and y.b=0
group by x.a
这是个思路
from [图(1)] x,[图(1)] y
where x.a=y.a
and x.b=1
and y.b=0
group by x.a
这是个思路
#2
--如果从上面的结果来统计,就是这样
select a
,E=cast(cast(sum(case b when 1 then c*100.0 else 0 end)
/sum(c) as decimal(10,2)) as varchar)+'%'
,F=cast(cast(sum(case b when 0 then c*100.0 else 0 end)
/sum(c) as decimal(10,2)) as varchar)+'%'
from [图(1)]
group by a
select a
,E=cast(cast(sum(case b when 1 then c*100.0 else 0 end)
/sum(c) as decimal(10,2)) as varchar)+'%'
,F=cast(cast(sum(case b when 0 then c*100.0 else 0 end)
/sum(c) as decimal(10,2)) as varchar)+'%'
from [图(1)]
group by a
#3
--如果你要直接出结果,就将你原来的语句帖出来
#4
如果[图(1)]是个汇总语句,如:
select a,b,count(*) from tablename
group by a,b
可以直接改为:
select a,sum(case when b=1 then 1.0 else 0.0 end)/sum(1.0),
sum(case when b=0 then 1.0 else 0 end)/sum(1.0)
from tablename
group by a
select a,b,count(*) from tablename
group by a,b
可以直接改为:
select a,sum(case when b=1 then 1.0 else 0.0 end)/sum(1.0),
sum(case when b=0 then 1.0 else 0 end)/sum(1.0)
from tablename
group by a
#5
原来的语句(图1的结果):
select a.co_name,c.if_app,count(*)
from AGENT_CO a,AGENT_CO_YEAR b,AGENT_CLIENT_DATA c
where a.level_no = b.level_no and a.co_no = b.co_no
and a.level_no = c.level_no and a.co_no = c.co_no
where a.level_no = '440601' and c.year_no = '20034'
group by a.co_name,c.if_app
select a.co_name,c.if_app,count(*)
from AGENT_CO a,AGENT_CO_YEAR b,AGENT_CLIENT_DATA c
where a.level_no = b.level_no and a.co_no = b.co_no
and a.level_no = c.level_no and a.co_no = c.co_no
where a.level_no = '440601' and c.year_no = '20034'
group by a.co_name,c.if_app
#6
楼主搞错了吧? 你那个语句根本就执行不了.
#7
--应该是这样写的才对:
select a.co_name
,E=cast(cast(sum(case c.if_app when 1 then 100.0 else 0.0 end)
/count(*) as decimal(10,2)) as varchar)+'%'
,F=cast(cast(sum(case c.if_app when 0 then 100.0 else 0.0 end)
/count(*) as decimal(10,2)) as varchar)+'%'
from AGENT_CO a
join AGENT_CO_YEAR b on a.level_no = b.level_no and a.co_no = b.co_no
join AGENT_CLIENT_DATA c on a.level_no = c.level_no and a.co_no = c.co_no
where a.level_no = '440601' and c.year_no = '20034'
group by a.co_name
select a.co_name
,E=cast(cast(sum(case c.if_app when 1 then 100.0 else 0.0 end)
/count(*) as decimal(10,2)) as varchar)+'%'
,F=cast(cast(sum(case c.if_app when 0 then 100.0 else 0.0 end)
/count(*) as decimal(10,2)) as varchar)+'%'
from AGENT_CO a
join AGENT_CO_YEAR b on a.level_no = b.level_no and a.co_no = b.co_no
join AGENT_CLIENT_DATA c on a.level_no = c.level_no and a.co_no = c.co_no
where a.level_no = '440601' and c.year_no = '20034'
group by a.co_name
#8
不会吧,有问题吗?我用的是sybase,没问题的,sqlserver同样也可以运行
我现在不在公司,不过大概就是这样子,不好意思上面语句搞错了:
select a.co_name,c.if_app,count(*)
from AGENT_CO a,AGENT_CO_YEAR b,AGENT_CLIENT_DATA c
where a.level_no = b.level_no and a.co_no = b.co_no
and a.level_no = c.level_no and a.co_no = c.co_no
and a.level_no = '440601' and b.year_no = '20034'
group by a.co_name,c.if_app
我现在不在公司,不过大概就是这样子,不好意思上面语句搞错了:
select a.co_name,c.if_app,count(*)
from AGENT_CO a,AGENT_CO_YEAR b,AGENT_CLIENT_DATA c
where a.level_no = b.level_no and a.co_no = b.co_no
and a.level_no = c.level_no and a.co_no = c.co_no
and a.level_no = '440601' and b.year_no = '20034'
group by a.co_name,c.if_app
#9
我大概明白你的意思,应该是这样,非常感谢
#10
你仔细看看你第一次帖出来的语句,居然有两个where,后面这个没问题了.
#11
结帖,明天早上回公司搞定它,有问题我会再来麻烦你,呵呵