I have a sql query (see below) which joins a project table to 3 other tables, the two left joins are fine and are returning what I need, the join on the document table will return a row for every document associated with the project, but I don't need the document details at all, I just need to return the count of how many documents are on the project, so effectively just want a single row, I don't know how to formulate this query, I know I need a subquery with a count, but the syntax defeats me, I attached an xls with the rows it has returned and beneath that I have added a row showing what I would like to achieve.
select p.project_code, p.name
, s.name as 'Project Status'
, r.Name as 'Division Name'
from project p
join Document_Entities de on de.Entity_Identifier = p.Project_ID
left join Project_Status s on p.Project_Status_ID = s.Project_Status_ID
left join region r on p.Region_ID = r.Region_ID
where p.Project_Code = 1036