CODEWITHSUNDEEP
sqlstored-proceduresgroup-by
Qasim Sarwar
Qasim Sarwar

Reputation: 1

Select query it is not contained in either an aggregate function or the GROUP BY clause

I am getting this error:

Msg 8120, Level 16, State 1, Procedure ReportEmployeesOnLeave, Line 17 Column 'Employee.EmployeeId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

enter image description here

Upvotes: 0

Views: 2300

Answers (2)

HoneyBadger
HoneyBadger

Reputation: 15150

What about the error message don't you understand? It's pretty self-explanatory. What it means is: If you use a group by, all the selected attributes have to be either in the group by or in an aggregate function (COUNT(), SUM() etc.).

So, if you correct your group by you shouldn't have any errors:

...
GROUP BY   TC.Date
,          TC.DayName
,          TC.DayType
,          TC.ScheduleId
,          TC.LeaveTaken
,          TC.Remarks
,          E.EmployeeId
,          E.EmployeeNo
,          E.Name
,          E.ScheduleId
,          C.BusinessName

Whether this gives the result you're looking for, is another question.

For future questions: Please add code as text, not as a picture. That way answerers can simply copy-paste, rather than typing it all over.

edit: If you don't want to group by all of your selected attributes you can use window functions (assuming sql server 2012 or above):

SELECT       * -- Everything you need
,            COUNT(TC.AbsentCalculation) OVER(PARTITION BY E.Name, E.EmployeeId) AS AbsentCount
,            SUM(TC.AbsentCalculation) OVER(PARTITION BY E.Name, E.EmployeeId) AS TotalAbsents
FROM         ...

You won't need a GROUP BY at all if you implement this.

Upvotes: 1

Priyank_Vadi
Priyank_Vadi

Reputation: 1138

Write all the fields into Group By cause

Upvotes: 0

Related Questions