Using Window Function instead of Select sub-query for counting

by Assaf   Last Updated May 15, 2019 14:06 PM

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 -

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


Related Questions

Updated July 26, 2017 00:06 AM

Updated February 16, 2016 00:15 AM

Updated April 16, 2017 03:06 AM

Updated April 04, 2015 00:02 AM

Updated August 02, 2016 08:02 AM