消息130,级别15,状态1,行1无法对包含聚合或子查询的表达式执行聚合函数。 [英] Msg 130, Level 15, State 1, Line 1 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
问题描述
实际查询
SELECT ((count(a.othrs)*((d.wages / 31)/ 8))) as otpay,(d.wages / 31)*(COUNT (*)) AS presentdaysal
FROM Tbl_Daily_Attendence a
inner join Tbl_DeptMaster_Creation d on d.DeptId = a.dept_id
其中 a.emp_id = ' E-00002 ' 和 a.attendencetype = ' P ' 和 a.cdate ' 2014-01-01' 和 '< /跨度> <温泉n class =code-string> 2014-01-31'
GROUP BY a.dept_id,d.wages,a.othrs
更改后如下所示获取值的总和
SELECT sum((count(a.othrs)*((d.wages / 31)/ 8)))as otpay,
总和(d.wages / 31)*(COUNT(*))AS presentdaysal
来自Tbl_Daily_Attendence a
内部联接tbl_DeptMaster_Creation d on d.DeptId = a.dept_id
其中a.emp_id ='E-00002'和a.attendencetype ='P'和a.cdate介于'2014-01-01'和'2014-01-31'之间
GROUP BY a.dept_id,d.wages,a.othrs
使用上面的查询我得到两行两列但是我想要一行添加这两行
OT Pay Prestsal
508.149193548387 4065.1935483871
645.161290322581 5161.29032258064
i想要上述内容as
Otpay礼物
1153.3 9226.48
试试这个:
< pre lang =sql> SELECT SUM(otpay)SumOfOtpay,SUM(presentdaysal) AS SumOfPresentdaysal
FROM (
SELECT ((count(a.othrs)*((d。 salary / 31)/ 8))) as otpay,(d.wages / 31)*(COUNT(*)) AS presentdaysal
FROM Tbl_Daily_Attendence a inner 加入 Tbl_DeptMaster_Creation d on d.DeptId = a.dept_id
其中 a.emp_id = ' E-00002' 和 a.attendencetype = ' P' 和 a.cdate ' 2014-01-01' 和 ' 2014-01-31'
GROUP BY a.dept_id,d.wages,a.othrs
) AS T
Actual Query
SELECT ((count(a.othrs) * ((d.wages/31)/8))) as otpay,(d.wages/31)* (COUNT (*)) AS presentdaysal
FROM Tbl_Daily_Attendence a
inner join Tbl_DeptMaster_Creation d on d.DeptId=a.dept_id
where a.emp_id='E-00002' and a.attendencetype ='P' and a.cdate between '2014-01-01' and '2014-01-31'
GROUP BY a.dept_id,d.wages ,a.othrs
After Change it like below to get sum of values
SELECT sum((count(a.othrs) * ((d.wages/31)/8))) as otpay,
sum(d.wages/31)* (COUNT (*)) AS presentdaysal
FROM Tbl_Daily_Attendence a
inner join Tbl_DeptMaster_Creation d on d.DeptId=a.dept_id
where a.emp_id='E-00002' and a.attendencetype ='P' and a.cdate between '2014-01-01' and '2014-01-31'
GROUP BY a.dept_id,d.wages ,a.othrs
by using above query i'm getting two rows and two columns but i want a single row with adding those two rows
OT Pay Prestsal
508.149193548387 4065.1935483871
645.161290322581 5161.29032258064
i want the above as
Otpay presentsal
1153.3 9226.48
Try this:
SELECT SUM(otpay) SumOfOtpay, SUM(presentdaysal) AS SumOfPresentdaysal FROM ( SELECT ((count(a.othrs) * ((d.wages/31)/8))) as otpay,(d.wages/31)* (COUNT (*)) AS presentdaysal FROM Tbl_Daily_Attendence a inner join Tbl_DeptMaster_Creation d on d.DeptId=a.dept_id where a.emp_id='E-00002' and a.attendencetype ='P' and a.cdate between '2014-01-01' and '2014-01-31' GROUP BY a.dept_id,d.wages ,a.othrs ) AS T
这篇关于消息130,级别15,状态1,行1无法对包含聚合或子查询的表达式执行聚合函数。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!