MS SQL nested query for counting records?

Question

SQL query optimization

Please help with a request. I don't understand how to calculate the number of records.
It is necessary to display the data in this format: Name, Department, Number of sales by a certain status.
Ivanov | Sales | 100
All entities are stored in different tables, but linked by contact ID.
Here is my request:

SELECT contact.Name AS NAME,
department.Name AS DEPARTMENT, 
COUNT(SELECT * FROM Orders WHERE Status='xxx') AS MYCOUNT  
FROM Contact contact JOIN Department department ON contact.DepartmentId= department.Id  
WHERE contact.DepartmentId ='9b90c192-60e6-df11-971b-001d60e938c6'

Answer 1

Modify the subquery by adding the necessary relationships and conditions to it (I can’t show the solution due to the lack of a complete diagram).

(SELECT COUNT(ID) FROM Orders WHERE Status='xxx') AS MYCOUNT