消息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屋！