David A. Long (1/16/2008)
Great article.BTW here id the fix for the original query:
SELECT C.CustomerID, C.CompanyName
, COUNT(O.OrderID) AS TotalOrders
FROM Customers C
LEFT OUTER JOIN Orders O ON C.CustomerID = O.CustomerID
AND O.OrderDate >= '19960101'
AND O.OrderDate < '19970101'
GROUP BY C.CustomerID, C.CompanyName
Andy
I think it also do the job:
SELECT C.CustomerID, C.CompanyName
, COUNT(O.OrderID) AS TotalOrders
FROM Customers C
LEFT OUTER JOIN Orders O ON C.CustomerID = O.CustomerID AND
YEAR(O.OrderDate) = 1996
--AND O.OrderDate >= '19960101'
--AND O.OrderDate < '19970101'
GROUP BY C.CustomerID, C.CompanyName
OR:
SELECT C.CustomerID, C.CompanyName, COUNT(O.OrderID) AS TotalOrders
FROM Customers C LEFT OUTER JOIN Orders O ON
C.CustomerID = O.CustomerID
WHERE YEAR(O.OrderDate) = 1996
GROUP BY ALL C.CustomerID, C.CompanyName