Column 'h.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Question

I need to return all of these columns but only where EmployeeID occurs once. I tried using group by having COUNT but get the error in the title. I have seen some examples doing this using only one or two columns in the select field but I am not sure how to structure this with more columns and do the group by on only EmployeeID.

SELECT DISTINCT i.EmployeeID, h.Name, h.AliasLastName, pp.Active, pp.DeptID, d.Name AS DepartmentName, 
                dpp.Title, hpp.Status, hpp.StatusDateTime,  i.HealthRecordID, i.ImmunizationDateTime 
FROM [liveNdb].dbo.HrEmpAuditHealthRecordImmunizations i
      INNER JOIN liveNdb.dbo.HrEmployees h
         ON h.SourceID = i.SourceID
            AND h.EmployeeID = i.EmployeeID
      INNER JOIN [liveNdb].dbo.HrEmployeePayrolls pp
         ON pp.SourceID = i.SourceID
            AND pp.EmployeeID = i.EmployeeID
      LEFT JOIN [liveATdb].dbo.MisGlDept_Main d
         ON d.SourceID = i.SourceID 
          and d.MisGlDeptID = pp.DeptID 
      LEFT JOIN liveNdb.dbo.DMisGlComponentValue cv 
         ON cv.SourceID = i.SourceID 
          and cv.ValueID = pp.DeptID 
      LEFT JOIN liveNdb.dbo.DPpPositions dpp
         ON dpp.SourceID = pp.SourceID
            AND (dpp.PositionID = pp.PositionNumberID OR
                 dpp.JobCodeID = pp.JobCodeID)
      LEFT JOIN [liveNdb].dbo.HrEmployeePersonelPositions hpp
       ON hpp.SourceID = pp.SourceID 
          AND hpp.EmployeeID = pp.EmployeeID
WHERE i.HealthRecordID LIKE 'COVID%'
     AND (hpp.Status IN ('ACTIVE', 'LEAVE', 'NEW')  OR d.Name = 'CHQ Administration')
GROUP BY i.EmployeeID having COUNT(*) = 1

Answer 1

When you use GROUP BY every column in the SELECT must either be an agregate or be named in the GROUP BY.
Do have the same results as DISTINCT we put everything into GROUP BY:

SELECT  i.EmployeeID, h.Name, h.AliasLastName, pp.Active, pp.DeptID, d.Name AS DepartmentName, 
                dpp.Title, hpp.Status, hpp.StatusDateTime,  i.HealthRecordID, i.ImmunizationDateTime 
FROM [liveNdb].dbo.HrEmpAuditHealthRecordImmunizations i
      INNER JOIN liveNdb.dbo.HrEmployees h
         ON h.SourceID = i.SourceID
            AND h.EmployeeID = i.EmployeeID
      INNER JOIN [liveNdb].dbo.HrEmployeePayrolls pp
         ON pp.SourceID = i.SourceID
            AND pp.EmployeeID = i.EmployeeID
      LEFT JOIN [liveATdb].dbo.MisGlDept_Main d
         ON d.SourceID = i.SourceID 
          and d.MisGlDeptID = pp.DeptID 
      LEFT JOIN liveNdb.dbo.DMisGlComponentValue cv 
         ON cv.SourceID = i.SourceID 
          and cv.ValueID = pp.DeptID 
      LEFT JOIN liveNdb.dbo.DPpPositions dpp
         ON dpp.SourceID = pp.SourceID
            AND (dpp.PositionID = pp.PositionNumberID OR
                 dpp.JobCodeID = pp.JobCodeID)
      LEFT JOIN [liveNdb].dbo.HrEmployeePersonelPositions hpp
       ON hpp.SourceID = pp.SourceID 
          AND hpp.EmployeeID = pp.EmployeeID
WHERE i.HealthRecordID LIKE 'COVID%'
     AND (hpp.Status IN ('ACTIVE', 'LEAVE', 'NEW')  OR d.Name = 'CHQ Administration')
GROUP BY i.EmployeeID, h.Name, h.AliasLastName, pp.Active, pp.DeptID, d.Name AS DepartmentName, 
                dpp.Title, hpp.Status, hpp.StatusDateTime,  i.HealthRecordID, i.ImmunizationDateTime
 having COUNT(*) = 1;

If you want to only group by i.EmployeeID in the GROUP BY use the following query, but check because it may not return what you're expecting. We use the agregation function MAX which works with all data types.

SELECT  i.EmployeeID, 
max(h.Name) AS "name", 
max(h.AliasLastName) aliasLastName, 
max(pp.Active) AS "Active", 
max(pp.DeptID) AS DeptID, 
max(d.Name) AS AS DepartmentName, 
max(dpp.Title) AS Title, 
max(hpp.Status) AS Status, 
max(hpp.StatusDateTime) AS StatusDateTime,  
max(i.HealthRecordID) AS HealthRecordID, 
max(i.ImmunizationDateTime) AS ImmunizationDateTime 
FROM [liveNdb].dbo.HrEmpAuditHealthRecordImmunizations i
      INNER JOIN liveNdb.dbo.HrEmployees h
         ON h.SourceID = i.SourceID
            AND h.EmployeeID = i.EmployeeID
      INNER JOIN [liveNdb].dbo.HrEmployeePayrolls pp
         ON pp.SourceID = i.SourceID
            AND pp.EmployeeID = i.EmployeeID
      LEFT JOIN [liveATdb].dbo.MisGlDept_Main d
         ON d.SourceID = i.SourceID 
          and d.MisGlDeptID = pp.DeptID 
      LEFT JOIN liveNdb.dbo.DMisGlComponentValue cv 
         ON cv.SourceID = i.SourceID 
          and cv.ValueID = pp.DeptID 
      LEFT JOIN liveNdb.dbo.DPpPositions dpp
         ON dpp.SourceID = pp.SourceID
            AND (dpp.PositionID = pp.PositionNumberID OR
                 dpp.JobCodeID = pp.JobCodeID)
      LEFT JOIN [liveNdb].dbo.HrEmployeePersonelPositions hpp
       ON hpp.SourceID = pp.SourceID 
          AND hpp.EmployeeID = pp.EmployeeID
WHERE i.HealthRecordID LIKE 'COVID%'
     AND (hpp.Status IN ('ACTIVE', 'LEAVE', 'NEW')  OR d.Name = 'CHQ Administration')
GROUP BY i.EmployeeID
 having COUNT(*) = 1;