How do I add a count() for one table into a SQL query that has joins for multiple other tables using SQLite?

I have a sql statement that pulls data from multiple tables. This query gets all employees and their biographic information (employee id, first name, last name, badge image, etc.):

SELECT
e.ID as id,
e.DateTimeCreated as dateTimeCreated,
e.FullTime as fullTime,
bi.FirstName as firstName,
bi.LastName as lastName,
bi.MiddleName as middleName,
bi.DateOfBirth as dateOfBirth,
bi.PlaceOfBirth as placeOfBirth,
fr.Thumbnail as thumbnail
FROM employee e
LEFT JOIN biographicinfo bi
ON e.ID = bi.employeeId
LEFT JOIN facialraw fr
ON e.ID = fr.EnrollmentId
AND fr.CaptureAngle = 2
GROUP BY e.ID

Now, there is another table called, say, EmployeeDiscrepencies that I would like to do a count on:

select count(1) as "Discrepencies" from EmployeeDiscrepencies ed where ed.employeeId = 2;

I would like to add this count statement into my original query when I grab all employees, but I cannot figure out how to do it. I wanted to try something like this:

SELECT
e.ID as id,
e.DateTimeCreated as dateTimeCreated,
e.FullTime as fullTime,
bi.FirstName as firstName,
bi.LastName as lastName,
bi.MiddleName as middleName,
bi.DateOfBirth as dateOfBirth,
bi.PlaceOfBirth as placeOfBirth,
fr.Thumbnail as thumbnail,
ed.count(1), -- need a "where" clause here
FROM employee e
LEFT JOIN biographicinfo bi
ON e.ID = bi.employeeId
LEFT JOIN facialraw fr
ON e.ID = fr.EnrollmentId
LEFT JOIN EmployeeDiscrepencies ed
on e.ID = ed.employeeID
AND fr.CaptureAngle = 2
GROUP BY e.ID

As you can guess, it is not working

How do I go about accomplishing this?

Answer

If you want only that counter from EmployeeDiscrepencies then the simplest way is to use a subquery to return it like this:

SELECT
e.ID as id,
e.DateTimeCreated as dateTimeCreated,
e.FullTime as fullTime,
bi.FirstName as firstName,
bi.LastName as lastName,
bi.MiddleName as middleName,
bi.DateOfBirth as dateOfBirth,
bi.PlaceOfBirth as placeOfBirth,
fr.Thumbnail as thumbnail,
(SELECT COUNT(*) FROM EmployeeDiscrepencies ed WHERE e.ID = ed.employeeID) as Discrepencies
FROM employee e
LEFT JOIN biographicinfo bi
ON e.ID = bi.employeeId
LEFT JOIN facialraw fr
ON e.ID = fr.EnrollmentId
AND fr.CaptureAngle = 2
GROUP BY e.ID

If your condition is for employeeId = 2 only then a cross join of your query to the subquery would be more efficient:

SELECT
e.ID as id,
e.DateTimeCreated as dateTimeCreated,
e.FullTime as fullTime,
bi.FirstName as firstName,
bi.LastName as lastName,
bi.MiddleName as middleName,
bi.DateOfBirth as dateOfBirth,
bi.PlaceOfBirth as placeOfBirth,
fr.Thumbnail as thumbnail,
ed.counter as Discrepencies
FROM employee e
CROSS JOIN (SELECT COUNT(*) count FROM EmployeeDiscrepencies ed WHERE  ed.employeeID = 2) ed
LEFT JOIN biographicinfo bi
ON e.ID = bi.employeeId
LEFT JOIN facialraw fr
ON e.ID = fr.EnrollmentId
AND fr.CaptureAngle = 2
GROUP BY e.ID