需求:
查询一个值(shiduanzonge)占总额(shiduanzongezj)的百分比,而这个值本身也是使用group by实现的一个合计值我写的sql:
select sum(shiduanzonge/(select sum(shiduanzonge) from gm_zhibiaoxinxi group by qiyeleixing)as shiduanzongezj) from gm_zhibiaoxinxi group by qiyeleixing错误通知:
消息 195,级别 15,状态 10,第 1 行
'sum' 不是可以识别的 内置函数名称。其中:
1. select qiyeleixing,sum(shiduanzonge) as shiduanzongezj from gm_zhibiaoxinxi group by qiyeleixing
2. select qiyeleixing,shiduanzonge/(select sum(shiduanzonge) from gm_zhibiaoxinxi group by qiyeleixing) as shiduanzongezj from gm_zhibiaoxinxi
这两个sql都是可以正常实现的。注:所有sql语句均在sql server下运行。希望能尽快解决,多谢各位高手指教!
查询一个值(shiduanzonge)占总额(shiduanzongezj)的百分比,而这个值本身也是使用group by实现的一个合计值我写的sql:
select sum(shiduanzonge/(select sum(shiduanzonge) from gm_zhibiaoxinxi group by qiyeleixing)as shiduanzongezj) from gm_zhibiaoxinxi group by qiyeleixing错误通知:
消息 195,级别 15,状态 10,第 1 行
'sum' 不是可以识别的 内置函数名称。其中:
1. select qiyeleixing,sum(shiduanzonge) as shiduanzongezj from gm_zhibiaoxinxi group by qiyeleixing
2. select qiyeleixing,shiduanzonge/(select sum(shiduanzonge) from gm_zhibiaoxinxi group by qiyeleixing) as shiduanzongezj from gm_zhibiaoxinxi
这两个sql都是可以正常实现的。注:所有sql语句均在sql server下运行。希望能尽快解决,多谢各位高手指教!
qiyeleixing,sum(shiduanzongezj) as shiduanzongezj
from
(
select qiyeleixing,sum(shiduanzonge) as shiduanzongezj from gm_zhibiaoxinxi group by qiyeleixing
)t
group by
qiyeleixing
from gm_zhibiaoxinxi AS a
group by qiyeleixing
SUM(shiduanzonge) AS [是否為100%]
FROM (
select qiyeleixing,
shiduanzonge*1.0/(select sum(shiduanzonge) from gm_zhibiaoxinxi WHERE qiyeleixing=a.qiyeleixing) AS shiduanzonge
from gm_zhibiaoxinxi AS a
group by qiyeleixing
)t
GROUP BY qiyeleixing
消息 8120,级别 16,状态 1,第 1 行
选择列表中的列 'gm_zhibiaoxinxi.shiduanzonge' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
select qiyeleixing,sum(case when shiduanzongezj=0 then 0 else 1.0*shiduanzonge/shiduanzongezj end)
from
(
select qiyeleixing,
shiduanzonge,
(select sum(shiduanzonge) from gm_zhibiaoxinxi where qiyeleixing=t.qiyeleixing) as shiduanzongezj
from gm_zhibiaoxinxi t
)tt
group by tt.qiyeleixing
select
qiyeleixing,
SUM(shiduanzonge)*1.0/(select sum(shiduanzonge) from gm_zhibiaoxinxi) AS shiduanzonge
from gm_zhibiaoxinxi AS a
group by qiyeleixing
qiyeleixing,
RTRIM(CAST(SUM(shiduanzonge)*100.0/(select sum(shiduanzonge) from gm_zhibiaoxinxi) AS DECIMAL(4,2)))+'%' AS shiduanzonge
from gm_zhibiaoxinxi AS a
group by qiyeleixing
測測這個顯示是否OK?
select
qiyeleixing,sum(shiduanzongezj/zongji)
from
(
(select qiyeleixing,sum(shiduanzonge) as shiduanzongezj from gm_zhibiaoxinxi group by qiyeleixing) a inner join (select sum(shiduanzonge) as zongji from gm_zhibiaoxinxi) b on(a.id=b.id)
) as t
group by
qiyeleixing错误提示:
消息 156,级别 15,状态 1,第 6 行
关键字 'as' 附近有语法错误。
消息 8115,级别 16,状态 6,第 1 行
将 float 转换为数据类型 numeric 时出现算术溢出错误。
leixing shiduanzonge
商贸 100%