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;
- 5. Grouping and Windowing
- from Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012
- Publisher: Microsoft Press
- Released: December 2012
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.
Share this highlighthttp://www.safaribooksonline.com/a/training-kit-exam/60185/