Thanks for this article. I learned something new and I will be using derived tables in the future. I would not consider myself an expert but I am wondering if you could handle this scenario using a case statement as follows:

SELECT C.CustomerID,

C.CompanyName,

Sum(Case when YEAR(O.OrderDate) = 1996 then 1 else 0 end) AS TotalOrders

FROM Customers C

LEFT OUTER JOIN Orders O ON

C.CustomerID = O.CustomerID

My questions are:

1. would this produce the correct results?

2. are there efficiency/index issues with this approach?

Thanks for your help