SQL Group By

[RosieGp]

I have the following query:

Create Procedure Emp_Data
@ID varchar(10)
AS
  select distinct Emp.ID, Emp.Employee_Name,Emp.Job_Status, 
EA.Employee_Address, EA.UpdatedAddress, EA.Update_Time, EW.TaxForms, EW.TFDate
  from Employee Emp
  left JOIN EmployeeAddress EA
      ON EA.ID = Emp.ID
  left JOIN EmployeeW2 EW
      ON EW.ID = Emp.ID
  where  ID = @ID
  group by Emp.ID, Emp.Employee_Name,Emp.Job_Status, 
EA.Employee_Address, EA.Update_Status, EA.Update_Time, EW.TaxForms, EW.TFDate

Now lets say If the same tax form was added 3 times at different times in a year, If use the above query it will show me 3 rows of data with different EW.TFDate but I want to eliminate the redundant data and tried the following:

Create Procedure Emp_Data
@ID varchar(10)
AS
  select distinct Emp.ID, Emp.Employee_Name,Emp.Job_Status, 
EA.Employee_Address, EA.UpdatedAddress, EA.Update_Time, EW.TaxForms, EW.TFDate
  from Employee Emp
  left JOIN EmployeeAddress EA
      ON EA.ID = Emp.ID
  left JOIN EmployeeW2 EW
      ON EW.ID = Emp.ID
  where  ID = @ID
  group by EW.TaxForms

But this throws the following error in SQL:

Msg 8120, Level 16, State 1, Emp_Data,
Column 'Emp.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How can I fix this, Please help.

 

[markros]

What is your SQL Server version? You may want to pick up one record based on the latest TFDate. You will find the solution to your problem, if you read carefully the first blog (and you may find the second blog helpful too):


Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2


PluralSight Learning Library

 

[TheBugSlayer]

Hi Rosie, you will get the same error for columns Emp.Employee_Name,Emp.Job_Status, EA.Employee_Address, EA.UpdatedAddress, EA.Update_Time, EW.TaxForms and EW.TFDate for the same reason: they need to be the argument of some aggregate function (SUM, COUNT, MIM, AVG, etc) or be referenced by the GROUP BY clause, which would not make much sense. If your EmployeeW2 table as an identity field, you could first retrieve a resultset with only one tax form per employee, then join it with the rest of the tables to retrieve the other fields in another resultset. There is probably a better solution but this is just a idea:

select Emp.ID, 
       Emp.Employee_Name,
       Emp.Job_Status, 
       EA.Employee_Address, 
       EA.UpdatedAddress, 
       EA.Update_Time, 
       EW.TaxForms, 
       EW.TFDate  
from Employee Emp  
left JOIN EmployeeAddress EA ON EA.ID = Emp.ID  
left JOIN EmployeeW2 EW  ON EW.ID = Emp.ID  
where EW.W2ID = (SELECT TOP 1 W2ID FROM EmployeeW2 WHERE EmpID = @ID ORDER BY W2ID)--ASC or DESC doesn' t really matter

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)