insert column that contains total counts grouped by values in one column

Question

I have a table departments that contains the name of a employees and the department they work in. I would like to add a column that contains the number of employees per department.

Name	Department	Count
Joe Doe	Clothing	2
John Doe	Clothing	2
Jane Doe	IT	1

There are two employees in the Clothing department and 1 in the IT department.

I know I can get the info like so in t-sql:

SELECT Department, count(*) AS Count FROM departments GROUP BY Department

What I'm unable to figure out is how to add this info to each row in the table departments.

Answer 1

Thanks to @squirrel, the following code works:

update d set [Count] = theCount
from
(
    select *, count(*) over (partition by Department) as TheCount
    from   departments
) d