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
        ,(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 -


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 -


  • Please update your question to include table definitions (DDL), sample data (in the form of insert statements) and the desired results. – Scott Hodgin May 15 at 13:27

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.

    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
    , E.LastName
    , C.AllOrders
    , C.LateOrders
    INNER JOIN dbo.Employees AS E ON E.EmployeeID = C.EmployeeID

Your Answer

