Help with my query

Hi,

I would like to get the data which return employee code associate with the order that is already assigned to them. But on those orders, I also would like to create different column counting how many orders are finished, and how many orders are still in process.

Can anyone help me with the query?

=============================================================

SELECT
e.EmployeeCode,
COUNT(oa.OrderID) as OrderCount
FROM Employee e
INNER JOIN OrderAssignment oa ON oa.EmployeeID = e.EmployeeID
WHERE e.DivisionCode = 'COM'
GROUP BY e.EmployeeCode

==============================================================

Above query will return the number of orders assigned to employee, however, I also need to get how many orders finished and how many orders still in process.

Thanks in advance.

SELECT
e.EmployeeCode,
COUNT(oa.OrderID) as OrderCount,

SUM(CASE WHERE oa.OrderStatus='Finished' THEN 1 ELSE 0 END) AS Finished,

SUM(CASE WHERE oa.OrderStatus='InProcess' THEN 1 ELSE 0 END) AS InProcess
FROM Employee e
INNER JOIN OrderAssignment oa ON oa.EmployeeID = e.EmployeeID
WHERE e.DivisionCode = 'COM'
GROUP BY e.EmployeeCode