Can we calculate the total Internet channel sales by date? Go ahead and run USE [AdventureWorksDW2012]; this query, and copy the resultset into Excel and draw some line charts. SELECT [OrderDate] ,COUNT([SalesOrderNumber]) as [Num Orders] Add some improvements to this query and resultset. What else can you , SUM([OrderQuantity]) as [Total Units Sold] filter the results by? What else can you group the totals on? Can you think ,FORMAT(SUM([SalesAmount]), 'N0') as [Total Revenue] of another filed to add sub-groups? FROM [dbo].[FactInternetSales] as i GROUP BY [OrderDate] ORDER BY [OrderDate] The SELECT TOP N syntax just cuts down the # of rows that are returned in USE [AdventureWorksDW2012]; the resultset after the GROUP BY and ORDER BY syntax do their job. It’s SELECT Top 10 CustomerKey probably a good idea to use the TOP N syntax on the same column as the , STR(SUM(SalesAmount)) AS Totals GROUP BY. You can perhaps select the top 2, top 5, top 10 records or any number you wish. You can see that this query would be the datasource for a FROM [dbo].[FactInternetSales] pareto chart of top 10 manufacturing defects. WHERE [OrderDate] BETWEEN '1/1/2008' AND '12/31/2009' This query performs the grouping and aggregating then just keeps the GROUP BY CustomerKey top10. You can also use TOP N Percent (ie TOP 5 PERCENT). IF you want the bottom 10 then perform a reverse sort (DESC) in your ORDER BY statement. ORDER BY Totals DESC Each of the GROUP BY queries to date can be further filtered using the TOP function, such as generating lists of top selling products, top performing customers, etc. The STR(field) is an old-school way to apply a little formatting to the number. This is fine for simple printing of resultsets from SSMS or Excel, but it actually converts the field to a string, which renders it useless in some reporting programs for subsequent calculations.
USE [AdventureWorksDW2012]; SELECT [OrderDate] ,COUNT([SalesOrderNumber]) as [Num Orders] , SUM([OrderQuantity]) as [Total Units Sold] ,FORMAT(SUM([SalesAmount]), 'N0') as [Total Revenue] FROM [dbo].[FactInternetSales] as i GROUP BY [OrderDate] ORDER BY [OrderDate] Can we calculate the total Internet channel sales by date? Go ahead and run this query, and copy the resultset into Excel and draw some line charts. Add some improvements to this query and resultset. What else can you filter the results by? What else can you group the totals on? Can you think of another filed to add sub-groups? USE [AdventureWorksDW2012]; SELECT Top 10 CustomerKey , STR(SUM(SalesAmount)) AS Totals FROM [dbo].[FactInternetSales] WHERE [OrderDate] BETWEEN '1/1/2008' AND '12/31/2009' GROUP BY CustomerKey ORDER BY Totals DESC The SELECT TOP N syntax just cuts down the # of rows that are returned in the resultset after the GROUP BY and ORDER BY syntax do their job. It’s probably a good idea to use the TOP N syntax on the same column as the GROUP BY. You can perhaps select the top 2, top 5, top 10 records or any number you wish. You can see that this query would be the datasource for a pareto chart of top 10 manufacturing defects. This query performs the grouping and aggregating then just keeps the top10. You can also use TOP N Percent (ie TOP 5 PERCENT). IF you want the bottom 10 then perform a reverse sort (DESC) in your ORDER BY statement. Each of the GROUP BY queries to date can be further filtered using the TOP function, such as generating lists of top selling products, top performing customers, etc. The STR(field) is an old-school way to apply a little formatting to the number. This is fine for simple printing of resultsets from SSMS or Excel, but it actually converts the field to a string, which renders it useless in some reporting programs for subsequent calculations.
Featherman-TSQL-Primer.docx