Execute the following SQL Server Transact-SQL scripts in Management Studio Query Editor to create a transaction summary report for (bicycle) frame products in 2004.

-- SQL common table expression - cte - SQL inner join group by - SQL inner join cte

-- SQL like wildcard search - SQL sum aggregate function - group by

USE AdventureWorks;

WITH cteTransactionHistory(ProductID,OrderQuantity)

     AS (SELECT   ProductID,

                  Sum(Quantity)

         FROM     Production.TransactionHistory

         WHERE    Year(TransactionDate) = 2004

         GROUP BY ProductID)

SELECT   [Products: Frame, 2004] = pp.Name,

         cte.ProductID,

         cte.OrderQuantity,

         ProductModel = pm.Name,

         SubCategory = ps.Name

FROM     Production.Product pp

         INNER JOIN cteTransactionHistory AS cte

           ON cte.ProductID = pp.ProductID

         INNER JOIN Production.ProductModel pm

           ON pm.ProductModelID = pp.ProductModelID

         INNER JOIN Production.ProductSubcategory ps

           ON pp.ProductSubCategoryID = ps.ProductSubCategoryID

WHERE    pp.Name LIKE ('%frame%')

         AND pp.ProductModelID IS NOT NULL

ORDER BY pp.Name

GO

/* Partial results

Products: Frame, 2004         ProductID   OrderQuantity     ProductModel

HL Road Frame - Black, 58     680         209               HL Road Frame

HL Road Frame - Red, 44       718         88                HL Road Frame

HL Road Frame - Red, 48       719         3                 HL Road Frame

HL Road Frame - Red, 58       706         213               HL Road Frame

*/