O'Reilly logo
  • Dustin Waybright thinks this is interesting:

he third workaround is to group and aggregate the rows from the Orders table first, define a table expression based on the grouped query, and then join the table expression with the Shippers table to get the shipper company names. Here’s the solution’s code.

WITH C AS
(
  SELECT shipperid, COUNT(*) AS numorders
  FROM Sales.Orders
  GROUP BY shipperid
)
SELECT S.shipperid, S.companyname, numorders
FROM Sales.Shippers AS S
  INNER JOIN C
    ON S.shipperid = C.shipperid;

From

Cover of Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012

Note

Using table expressions to organize the categorical data and then join grouped analysis data to the categorical data. This is a solution I use frequently in different forms because of the flexibility.