SQL Server basics - where clause -

this code have:

use tsql2012  select      emp.empid, emp.firstname, emp.mgrid,      manager.firstname manager_name,     sum(unique(emp.mgrid)) total      hr.employees emp, hr.employees manager      emp.mgrid = manager.empid go 

and result supposed is:

   empid firstame mgrid manager_name total 1   2   don        1    sara          1 2   3   judy       2    don           2 3   4   yael       3    judy          2 4   5   sven       2    don           2 5   6   paul       5    sven 6   7   russell    5    sven 7   8   maria      3    judy 8   9   zoya       5    sven 

i can't last column (total). appreciated

one way of doing use of cross apply:

select emp.empid, emp.firstname, emp.mgrid, manager.firstname manager_name,         x.cnt total employees emp inner join employees manager on emp.mgrid = manager.empid cross apply (    select count(*)    employees e    e.mgrid = emp.mgrid) x(cnt)  

demo here

edit:

you can use cte in way:

;with c (     select b.mgrid, count(*) total     employees b     group mgrid  )  select emp.empid, emp.firstname, emp.mgrid, manager.firstname manager_name,         total  employees emp inner join employees manager on emp.mgrid = manager.empid inner join c on emp.mgrid = c.mgrid 

demo here