使用聚合函数 [英] Using the Aggregate Function

问题描述

朋友们，

我正在尝试编写一个查询，我必须加入三个表并在网格中显示员工姓名和总和（估计小时数）和总和（实际小时数）之间的差异。

但是在执行以下代码时，会显示以下错误列'Tasks'Emp_ID'在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中 。

hi friends,
I am trying to write a query where i have to join three tables and to display Employee name and Difference between Sum(Estimated Hours) and Sum(Actual Hours) in a grid.
But on executing the following code , the following Error is shown " Column 'Tasks.Emp_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause".

SELECT  
	  
	  Tasks.[Emp_ID],
	  EMPLOYEE.[Emp_Name],
	  Department.[Dept_ID],
	  Department.[Dept_Name],
	  (Sum(Estimated_Hours)-  Sum(Actual_Hours))as Total ,
	  Status_Id
	 	 
	  FROM Tasks   
	  
	  Left Join Employee
	  ON EMPLOYEE.[Emp_ID] = Tasks.[Emp_ID]
	  
	  
	  Left JOIN Department
	  ON EMPLOYEE.Dept_ID = Department.Dept_ID
	  
	  Where Tasks.Status_Id ='1' Or Tasks.Status_Id ='2'
	  Group By Employee.[Emp_Name]	  
    End

有人帮助我...

先谢谢

someone help me out ...
Thanks in Advance

推荐答案

试试这个

try this

select 
Tasks.[Emp_ID],
EMPLOYEE.[Emp_Name],
Department.[Dept_ID],
Department.[Dept_Name],
(Sum(Estimated_Hours)- Sum(Actual_Hours))as Total ,
Status_Id

FROM Tasks 

Left Join Employee
ON EMPLOYEE.[Emp_ID] = Tasks.[Emp_ID]


Left JOIN Department
ON EMPLOYEE.Dept_ID = Department.Dept_ID

Where Tasks.Status_Id ='1' Or Tasks.Status_Id ='2'
Group By Employee.[Emp_Name],Tasks.[Emp_ID],
EMPLOYEE.[Emp_Name],
Department.[Dept_ID],
Department.[Dept_Name],
Status_Id

End

这篇关于使用聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！