Is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Question

I was creating a query, and somehow there seems to be a problem in my query.

Here is my query:

Select
E.last_name as [Last Name],
E.first_name as [First Name],
SUM(CASE WHEN empAttendance.status = 'Absent' THEN 1 ELSE 0 END) as [Absences],
SUM(CASE WHEN empAttendance.status = 'Late' THEN 1 ELSE 0 END) as [Number of Lates]
from
empAttendance
INNER JOIN employee E ON empAttendance.emp_id = E.emp_id
WHERE E.company_id = (Select company_id from company Where company_name = @company)

Wherein the employee table has the emp_id column which is its PK and also the empAttendance has the emp_id as FK.

employee table has the last_name and the first_name columns.

The error says : Column 'employee.last_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Thanks in advance.

Answer 1

When using aggregate function SUM you need to group by:

Select
E.last_name as [Last Name],
E.first_name as [First Name],
SUM(CASE WHEN empAttendance.status = 'Absent' THEN 1 ELSE 0 END) as [Absences],
SUM(CASE WHEN empAttendance.status = 'Late' THEN 1 ELSE 0 END) as [Number of Lates]
from
empAttendance
INNER JOIN employee E ON empAttendance.emp_id = E.emp_id
WHERE E.company_id = (Select company_id from company Where company_name = @company)
GROUP BY E.last_name, E.first_name