Redyne Redyne - 1 month ago 7
SQL Question

SQL using select with group by

SELECT manager_id, COUNT(manager_id)
FROM employees
GROUP BY manager_id
HAVING COUNT(manager_id) > 3


I have no problem with the code, but,I want to display the manager first name and last name instead of the manager_id, however if I do this, I get the error:
"column 'employees.first_name' is invalid... not contained in either an aggregate function or group by clause". I tried adding first_name and last_name to the group by, output being blank. Tried self join as well.. can't figure out the answer.
The query includes: employee_id, first_name, last_name, manager_id

The query above displays:

manager_id (No column name)
100 14
120 8
121 8
122 8
123 8
124 8
145 6
146 6
147 6
148 6
149 6


I want:

first_name last_name

Bob Smith

Answer
    SELECT ISNULL(m.name, 'None') AS ManagerName, COUNT(1)
    FROM employees e
    LEFT JOIN employees m
    on e.manager_id = e.employee_id
    GROUP BY ISNULL(m.name, 'None')
    HAVING COUNT(1) > 3

Note count(1) is the same as count(*) but slightly less work on the server.