SELECT Statement with correlated/nested Subquery

Question

I have two tables in my database, Department & DepartmentErrors. The DepartmentErrors table contains a column called 'Error

I would like to run a select statement on the Department table, matching any related occurrences of that department within DepartmentError, where the Error value matches a number. I would then like to append a column onto that result set, for each department, containing how many rows matching that department ID & Error value have appeared within the department error table. This is my code so far:

SELECT DISTINCT
    Department.DeptID,
    Name,
    Size,
    Location,
    (
        SELECT COUNT(*)
        FROM DepartmentErrors
        INNER JOIN Departments ON DepartmentErrors.DeptID = Departments.DeptID
        WHERE Error = 2
    ) AS ErrorCount
FROM Departments
INNER JOIN DepartmentErrors ON Departments.DeptID = DepartmentErrors.DeptID
WHERE DepartmentErrors.Error = 2

Answer 1

Try this one -

SELECT d.DeptID,
       d.Name,
       d.Size,
       d.Location,
       e.ErrorCount
FROM Departments d
JOIN (
    SELECT DeptID, ErrorCount = COUNT(*)
    FROM DepartmentErrors
    WHERE Error = 2
    GROUP BY DeptID
) e ON d.DeptID = e.DeptID