Great article. You might want to include some things though. I use a CTE to get me out of awkward JOIN situations. If I want to count the number of Customers assigned to a Worker and print the name from the table [Worker] I have to add every stinking column to the GROUP BY. But with a CTE I can take this preaggregate: SELECT WorkerId, COUNT(CustomerId)

FROM WorkerCustomer

GROUP BY UserId

and the JOIN to it. Since the GROUP BY only applies to the sub-query.

Also the syntax allows you to specify the column names coming out so that I don't have to put the AS after the COUNT(). It looks like this: WITH UC (UserId, CustomerCount) AS ( ... )

But then I needed two CTEs in the same query. That syntax is a bit on the awkward side (IMHO). You specify the first CTE then a comma and the second CTE without the WITH. The whole thing is below.

WITH WC (WorkerId, CustomerCount)

AS (

SELECT WorkerId, COUNT(CustomerId)

FROM WorkerCustomer

GROUP BY WorkerId

)

, WO (WorkerId, OrderCount)

AS (

SELECT WorkerId, COUNT(OrderNumber)

FROM [SalesOrder]

GROUP BY WorkerId

)

SELECT

[Worker].Name

,[Worker],Supervisor

,COALESCE(WC.CustomerCount,0) AS [Customer Count]

,COALESCE(WO.OrderCount,0) AS [Order Count]

FROM [Worker]

LEFT OUTER JOIN WC ON WC.WorkerId = [Worker].ID

LEFT OUTER JOIN WO ON WO.WorkerId = [Worker].ID

ORDER BY WC.CustomerCount DESC, WO.OrderCount DESC