I have to count the number of Orders a table, per Employee, for two criteria -
- "Late Orders".
- "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 -