Execute the following SQL Server T-SQL script in SSMS Query Editor to list yearly sales by product selling at and over $200,000:

-- T-SQL GROUP BY CTE with SUM aggregate

USE AdventureWorks2008;

WITH cteYearlyProductSales(ProductID,YearOfOrder,YearlySales)

     AS (SELECT   D.ProductID,

                  YEAR(H.OrderDate),

                  SUM(D.LineTotal)

         FROM     Sales.SalesOrderDetail D

                  INNER JOIN Sales.SalesOrderHeader H

                    ON D.SalesOrderid = H.SalesOrderid

         GROUP BY D.ProductID,

                  YEAR(H.OrderDate))

SELECT   YEAR = S.YearOfOrder,

         ProductName = P.Name + '(' + convert(VARCHAR,P.ProductID) + ')',

         YearlySales = convert(NUMERIC(10,0),S.YearlySales)

FROM     cteYearlyProductSales S

         INNER JOIN Production.Product P

           ON S.ProductID = P.ProductID

WHERE    S.YearlySales >= 200000

ORDER BY YEAR,

         ProductName

GO

/*

YEAR  ProductName                   YearlySales

2001  Mountain-100 Black, 38(775)   700113

2001  Mountain-100 Black, 42(776)   660893

2001  Mountain-100 Black, 44(777)   733295

2001  Mountain-100 Black, 48(778)   623698

2001  Mountain-100 Silver, 38(771)  688158

2001  Mountain-100 Silver, 42(772)  592814

2001  Mountain-100 Silver, 44(773)  629140

……

*/

Related article:

Find top sales people