Using Window Function instead of Select sub-query for counting

Question

I have to count the number of Orders a table, per Employee, for two criteria -

1. "Late Orders".
2. "Total Orders"

The query includes Orders table and Orders table. They are joined together via the EmployeeID field. The whole schema is found here.

This can be done the following way -

    Select E.EmployeeID
        ,E.LastName
        ,(Select Count(*) from Orders where Orders.EmployeeID = E.EmployeeID) as AllOrders
        ,count(OrderID) LateOrders

from Orders inner join Employees E on E.EmployeeID = Orders.EmployeeID
where CONVERT(date,RequiredDate) <= CONVERT(date,ShippedDate)
group by E.EmployeeID, E.LastName
order by EmployeeID 

However I want to replace the following row with a window function -

    ,(Select Count(*) from Orders where Orders.EmployeeID = E.EmployeeID) as AllOrders

What I tried to do didn't work, with the following error -

Count(*) over(Partition By Orders.EmployeeID)

Column 'Orders.EmployeeID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I'd like to practice window functions. Can I use it here?

Here's the output -

Answer 1

I think that this would provide the best performance while still avoiding the sub-select. I guessed that there was a column called OrderID in the Orders table, may need to adjust.

;WITH CTE_Orders AS
    (
    SELECT O.EmployeeID
        , COUNT(O.OrderID) AS AllOrders
        , SUM(CASE WHEN CONVERT(DATE, O.RequiredDate) <= CONVERT(DATE, O.ShippedDate) THEN 1 ELSE 0 END) AS LateOrders
    FROM dbo.Orders AS O
    GROUP BY O.EmployeeID
    )
SELECT E.EmployeeID
    , E.LastName
    , C.AllOrders
    , C.LateOrders
FROM CTE_Orders AS C
    INNER JOIN dbo.Employees AS E ON E.EmployeeID = C.EmployeeID
ORDER BY C.EmployeeID