1

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 -

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.