count() results without using group by

I am attempting something very similar to last example (Using GROUP BY) on this page: https://thecodedeveloper.com/mysql-count-function/

Referring to the following table of data:

id   name   salary   department
 1   Tom    4000     Technology
 2   Sam    6000     Sales
 3   Bob    3000     Technology
 4   Alan   8000     Technology
 5   Jack   12000    Marketing

The following query:

SELECT department, COUNT(*) AS "Number of employees"
FROM employees
GROUP BY department;

Will produce the following output:

department    Number of employees
Marketing        1
Sales            1
Technology       3

Except I want to see the number of employees in each department as well as every user in the table.

So I want the output to look like this:

id   name   salary   department   employees per department
 1   Tom    4000     Technology        3
 2   Sam    6000     Sales             1
 3   Bob    3000     Technology        3
 4   Alan   8000     Technology        3
 5   Jack   12000    Marketing         1

I have managed to achieve what I want using a second query to test every result from the first query but it is extremely slow and I am convinced that there is a faster way to do it in a single query.

Answer

That’s a window count. In MySQL 8.0:

select e.*, count(*) over(partition by d.department) as number_of_employees
from employees e

In earlier versions, an alternative uses a correlated subquery:

select e.*,
    (select count(*) from employees e1 where e1.department = e.department) as number_of_employees
from employees e