Execute the following Microsoft SQL Server T-SQL script in SSMS Query Editor to demonstrate the use of GROUP BY on a CTE to create summary report.

-- SQL Server CTE - T-SQL CTE - SQL Server Common Table Expression

-- SQL Server GROUP BY with CTE

USE AdventureWorks2008;

WITH cteSales AS

(

  SELECT

         p.[FirstName] + ' ' + COALESCE(p.[MiddleName], '') + ' ' +

         p.[LastName] AS [FullName]

        ,e.[JobTitle]

        ,st.[Name] AS [SalesTerritory]

        ,soh.[SubTotal]

        ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear]

        ,soh.[SalesPersonID]

    FROM [Sales].[SalesPerson] sp

        INNER JOIN [Sales].[SalesOrderHeader] soh

                  ON sp.[BusinessEntityID] = soh.[SalesPersonID]

        INNER JOIN [Sales].[SalesTerritory] st

                  ON sp.[TerritoryID] = st.[TerritoryID]

        INNER JOIN [HumanResources].[Employee] e

                  ON soh.[SalesPersonID] = e.[BusinessEntityID]

        INNER JOIN [Person].[Person] p

                  ON p.[BusinessEntityID] = sp.[BusinessEntityID]

)

SELECT SalesTerritory, FiscalYear, FullName,

      Total = '$'+convert(varchar,SUM([SubTotal]),1)

FROM cteSales c

GROUP BY SalesTerritory, FiscalYear, FullName

ORDER BY SalesTerritory, FiscalYear, FullName

GO

Partial results: