Carson College of Business



Featherman’s Advanced T-SQL Analytics – Window Functions? 1-4-2017Window functions have nothing to do with Microsoft Windows, rather Window Functions are T-SQL syntax that are used to specify sub-groupings within groupings of data, and more importantly window functions specify calculations within the sub-groupings of data. For example the invoices of a sorted customer list can be displayed with a running total, and when the next customer is shown, a new window is created and the running total starts again for that customer.Window functions are used to build datasets that can then be sliced and diced in a multitude of ways. Window functions are then a useful part of the ETL pipeline of data – used to aggregate large amounts of data into more compact (i.e., less rows) datasets that serve as the datasource for subsequent reporting and dashboards.A great deal of what an analyst does, is to take business problems and then to identify, source, clean and merge data that is needed to investigate problems or opportunities, monitor processes. Often new calculations or derived data is needed to better track problems. It’s best to build the queries that find and merge and aggregate the data and to save the queries so they can be part of an automated pipeline of data that refreshes dashboards that are displayed on webpages, dashboards, tablets and other mobile devices. Often the queries get quite complex in calculations and number of columns of calculations, so the analyst often resorts to creating parameterized reports that ‘call’ the stored procedure, for example allowing analysis using filters of geography. Other times the analyst or DBA wants to aggregate data in to large datasets and just pass access to the dataset to a group of analysts. For example each regional sales manager may want their own dashboard of reports, that re filtered for the region. Rather than create a series of ‘hard-coded for region’ dashboards, the DBA or To date the calculations have been performed for one group or several groups within the resultset. But what if you wanted to add a running total or percentage of total? The T-SQL we have covered to date has no idea where one group ends and another begins. It is necessary to perform the calculations on sub-groups within the larger resultset. Sure you can run a bunch of separate queries (or a parameterized query) to calculate the running total or percent of total calculation for any single group, but that isn’t realistic? For example if you perform a breakdown of sales based on color by region of the US, you could use a PIVOT() query of region by color, however you would have to perform many of these queries since there is only one analytic provided per PIVOT() query (for example you would need one query for the unit count of sales and another for the dollar total of sales). There are dozens more analytics that would be useful as well, so you would need dozens of queries. On the other hand you could execute a GROUP BY() query but again you would need one group by query for each region and there are many regions of the USA. What we need is a grouping mechanism within the grouping mechanism. SQLServer 2012 introduced Window functions (windows or sub-groupings of the resultset) to solve this problemTherefore this document demonstrates some WINDOW functions of SQL Server, and uses them to solve many typical problems. Window functions are another form of grouping data so that calculations start over for every new group (i.e. product subcategory within product category). For example a moving average should start over when the product subcategory changes from road bikes to mountain bikes. The examples are based on AdventureWorksDW2012. So let’s get started. Begin by copying & pasting the base query below into a new SQL Server query to make a base table. We will add columns of derived data analytics to this base query. Examine the result set and envision the analytics that can be run from it. For each added calculated field try to envision another problem context that could be solved with this new functionality.In this project – we create several extra calculated columns of derived data, which are later used in charts, pivot tables and reports. So the goal here is to create a powerful query that can be saved as a view or perhaps parameterized and saved as a stored procedure. The final query can be saved and re-used and serve as the data source for dashboards (ie envision line charts based on month). The column values are re-calculated each time the query is run. It is preferable to create these calculated fields in a database query (stored as a view) or in a data warehouse table (that a report can easily connect to) rather than in a report. So you can choose different implementations of the queries in this document – you can save the query as a view (virtual table) since you cannot store every piece of data required for analysis in a column often you calculate and derive the value ‘on the fly’. You can also save the query as part of an extract, transform and load data process, extracting data from one database table, changing it and loading the compiled resultset into the data warehouse. Just don’t do these calculations in a query that is inside a report or use the query builder inside a report. The concern there is that you have to re-create the query inside the next report as well. Powerful derived fields as shown here should be discussed by the DBA’s, analysts and management team and institutionalized as IT assets (not buried in a random report that an analyst creates and doesn’t document). So this document introduces the participant to ‘window functions’ of SQL. There actually isn’t a specific T-SQL syntax ‘Window’ rather the OVER() clause is used to define a window (a sub-grouping) of records within which to perform analytics on a group. We are consolidating 60,000 rows with the GROUP BY and then further breaking the rows into groups called windows.Base Query – Please copy this base query syntax into a new query in SSMS and run it. We will add several calculated columns next.You can check the appendix to see an explanation of the base query.USE [AdventureWorksDW2012];SELECT [EnglishProductSubcategoryName] AS [SubCategory], YEAR(i.[OrderDate]) AS [Year], DATEPART(MONTH, i.OrderDate) AS [Month#], DATENAME(MONTH, i.OrderDate) AS [MonthName], COUNT(i.[OrderQuantity]) AS [Monthly Qty], SUM(i.[SalesAmount])AS [Monthly Revenue], SUM([SalesAmount] - [TotalProductCost]) as [Profit]FROM [dbo].[FactInternetSales] as iINNER JOIN [dbo].[DimProduct] as p ON p.[Productkey] = i.[Productkey]INNER JOIN [dbo].[DimProductSubcategory] as s ON s.[ProductSubcategoryKey]= p.[ProductSubcategoryKey]INNER JOIN [dbo].[DimProductcategory] as c ON c.[ProductCategoryKey]= s.[ProductCategoryKey]WHERE i.[OrderDate] BETWEEN '1/1/2006' and '12/31/2007' AND [EnglishProductCategoryName] = 'Bikes'GROUP BY [EnglishProductSubcategoryName], YEAR(i.[OrderDate]), DATEPART(MONTH, i.OrderDate) , DATENAME(MONTH, i.OrderDate) ORDER BY [EnglishProductSubcategoryName] ,YEAR(i.[OrderDate]) , DATEPART(MONTH, i.OrderDate) Notice there are different Product Subcategories and the sales are lined up by month within the sub-categories.If you used this base query to create a view and called the view from Excel, then you could use a pivot table to cross analyze the sales by region to investigate whether sales are regional (ie. does Asia buy a lot of Bikes in January and America buys a lot of Bikes in June?). Armed with this information the brand manager can plan a promotional campaign or a ‘special edition’ product. (or at least make more efficient bulk shipping decisions to pre-place the products in the correct regions to handle the peak sales times. )FYI – If you added the productalternatekey, and the englishproductname fields to the SELECT and GROUP BY statements, then you would be able to analyze the profitability of certain products or product lines in the product line. You probably would want to reduce the dataset to filter down to just one sub-category at a time. In this way you can make decisions whether to expand or contract the product line.Running Totals - Add this code before the FROM statement of the base query to add a running total. Running totals are great if you want to create a combo chart as shown below. You may say hey, I don’t need this difficult content, I can create charts from downloaded data any time I want. Sure says the DBA, but can you automate the process? If we create the SQL queries and then call them from SSRS or Excel then the entire reporting or dashboard process can be automated, which means no employee ever needs to hand-make the charts again. DBA’s can also set the data to refresh for a report or spreadsheet at pre-determined time intervals.-- this is a running total that resets for each year within a SubCategory,SUM(SUM(SalesAmount) ) OVER(PARTITION BY [EnglishProductSubcategoryName], YEAR(OrderDate)ORDER BY DATEPART(MONTH, OrderDate) RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Running Sales Total for Year]-- this is a running total that resets for each SubCategory ,SUM(SUM(SalesAmount) ) OVER(PARTITION BY [EnglishProductSubcategoryName]ORDER BY YEAR(OrderDate), DATEPART(MONTH, OrderDate) RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Running Sales Total to date]This first window function creates a calculated field that has a running total that restarts every year and every product subcategory. So the Partition By has 2 columnsPlease notice the difference in the second window function and the derived calculations from that. It is a similar running total but it only resets per product subcategory not per year. So the partition by has only 1 column. Since there is more data in the running total and the same month numbers can repeat we need to add year to the ORDER BY phrase Note: The chart is a combination chart, a secondary axis should be added to it.This would be a great dataset to throw at Tableau.Should we try to move the data to Excel and create a combo chart? Should we convert the data to weekly totals?61302932278Line by line explanation:,SUM(SUM([SalesAmount])) The first sum indicates that we are summing values – the running total over the window.The second sum is used since inside each grouping partition (read next section) we are consolidating data using a GROUP BY query. We are consolidating 60,000 rows with the GROUP BY and then further breaking the rows into groups called windows.OVERThe OVER “Determines the partitioning and ordering of a rowset before the associated window function is applied. That is, the OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.”OVER starts the specification of the partition which is the category (the field) that is being used to confine the running total. When do you reset the running total back to zero? In this case, when the sub-category changes or the year within a sub-category changes. (PARTITION BY [EnglishProductSubcategoryName], YEAR(OrderDate)“Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.”ORDER BY DATEPART(MONTH, OrderDate)Specifies column for running calculation, Here on the derived field is Month # (1, 2, 3, etc.) RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)The Range specifies what rows to total, here total all the preceding rows in the partition and add to it the value in the current row.AS [Running Total]This entire code block was used to create a calculated column called running total. Was it worth it? Yes, because many different reports can be derived from this query.Also the query can be re-used (until someone changes the column names or table structure).Compare the fields in the partition by and the ORDER BY portion of the window function – they are working together with the dimensions in the base query’s GROUP BY statement.Window Functions – Another Running Totals ExampleUSE [AdventureWorksDW2012];SELECT [EnglishProductSubcategoryName] AS [SubCategory], DATEPART(MONTH, i.OrderDate) AS [Month#], DATENAME(MONTH, i.OrderDate) AS [Month], COUNT(i.[OrderQuantity]) AS [Monthly Qty], SUM(i.[SalesAmount])AS [Monthly Revenue], SUM([SalesAmount] - [TotalProductCost]) as [Profit],SUM(SUM([SalesAmount])) OVER (PARTITION BY [EnglishProductSubcategoryName] ORDER BY DATEPART(MONTH, i.OrderDate) RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Revenue Running Total],SUM(SUM([SalesAmount] - [TotalProductCost])) OVER (PARTITION BY [EnglishProductSubcategoryName] ORDER BY DATEPART(MONTH, i.OrderDate) RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Profit Running Total]FROM [dbo].[FactInternetSales] as iINNER JOIN [dbo].[DimProduct] as p ON p.[Productkey] = i.[Productkey]INNER JOIN [dbo].[DimProductSubcategory] as s ON s.[ProductSubcategoryKey]= p.[ProductSubcategoryKey]INNER JOIN [dbo].[DimProductcategory] as c ON c.[ProductCategoryKey]= s.[ProductCategoryKey]WHERE YEAR(i.[OrderDate]) = 2008 AND c.ProductCategoryKey = 1GROUP BY [EnglishProductSubcategoryName], DATEPART(MONTH, i.OrderDate) , DATENAME(MONTH, i.OrderDate) ORDER BY [Subcategory], DATEPART(MONTH, i.OrderDate) Here is an initial example – please run the query on the left:/* Here a running total of the [SalesAmount] column is totaled for each month in each product subcategory group. Notice the running total resets for each change in ProductSubcategory. The fields in the partition by are some (not necessarily all) the fields in the GROUP BY */OVER determines the partitioning and ordering of a rowset before the associated window function is applied. That is, the OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.PARTITION BY clause divides the result set produced by query (specifically by the GROUP BY clause) into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. ORDER BY specifies the columns to use to calculate the running totals order by clause is required.20667913603630018312971597600So this is just a running total by sub-category. Since the rows of data are by month, there are many rows of data that are in the running total.USE [AdventureWorksDW2012];SELECT [EnglishCountryRegionName] as [Country], [EnglishProductSubcategoryName] AS [SubCategory], DATEPART(MONTH, rs.OrderDate) AS [Month#], DATENAME(MONTH, rs.OrderDate) AS [Month], COUNT(rs.[OrderQuantity]) AS [Monthly Qty], SUM(rs.[SalesAmount])AS [Monthly Revenue], SUM([SalesAmount] - [TotalProductCost]) as [Profit]/* Here a running total of the [SalesAmount] column is totaled for each month in each product subcategory group. Notice the running total resets for each change in ProductSubcategory. The fields in the partition by are some (not necessarily all) the fields in the GROUP BY */,SUM(SUM([SalesAmount])) OVER (PARTITION BY [EnglishCountryRegionName], [EnglishProductSubcategoryName]ORDER BY DATEPART(MONTH, rs.OrderDate) RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Revenue Running Total],SUM(SUM([SalesAmount] - [TotalProductCost])) OVER (PARTITION BY [EnglishCountryRegionName], [EnglishProductSubcategoryName]ORDER BY DATEPART(MONTH, rs.OrderDate) RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Profit Running Total]FROM [dbo].[FactResellerSales]as rsINNER JOIN [dbo].[DimReseller] as r ON r.ResellerKey = rs.ResellerKeyINNER JOIN [dbo].[DimGeography] as g ON g.[GeographyKey] = r.GeographyKeyINNER JOIN [dbo].[DimProduct] as p ON p.[Productkey] = rs.[Productkey]INNER JOIN [dbo].[DimProductSubcategory] as s ON s.[ProductSubcategoryKey]= p.[ProductSubcategoryKey]INNER JOIN [dbo].[DimProductcategory] as c ON c.[ProductCategoryKey]= s.[ProductCategoryKey]WHERE YEAR(rs.[OrderDate]) = 2008 AND c.ProductCategoryKey = 1GROUP BY [EnglishCountryRegionName],[EnglishProductSubcategoryName], DATEPART(MONTH, rs.OrderDate) , DATENAME(MONTH, rs.OrderDate) ORDER BY [Country], [SubCategory], DATEPART(MONTH, rs.OrderDate) This query adds region which helps to really dig into the analysis. You can use region as a slicer.Also with a focus on profit it is very interesting to see that sales are often pushed at the expense of profit.Sure it’s good to keep the factory working, but if we are losing money in some regions, it’s time to take a closer look, and make some changes.What are the reasons to have good sales amounts yet lose money?Go ahead and replicate some of these charts in Excel using a pivot chart.Moving average - Add this code before the FROM statement of the base query to add a calculated column that displays a moving average. Moving averages are used to smooth out temporary fluctuations in values. As a manager, rather than ‘knee-jerk’ react to temporary dips in performance, it may be prudent to step back and keep partially focused on a more summary trend. Moving averages smooth out the peak and valley fluctuations in the data. Sales people love this reporting tool so that they don’t get hammered often. You can also add a moving average to excel spreadsheets.-- This is a 3 month moving average. --The current row plus 2 preceding = 3 rows total.,AVG(SUM(SalesAmount)) OVER (PARTITION BY ([EnglishProductSubcategoryName]) ORDER BY DATEPART(MONTH,OrderDate)ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)AS [Revenue 3 Month Avg.]Line by line explanation:, AVG(SUM(SalesAmount)) OVER The AVG syntax delineates that we will calculate an average of the summed records over the partition window. The Sum(fieldname) is needed as we are consolidating 60,000 rows into groups based on the month.) So we are averaging the monthly sums and starting over each new sub-category.(PARTITION BY ([EnglishProductSubcategoryName]) Break the windows by Subcategory (this is a grouping mechanism)ORDER BY DATEPART(MONTH,OrderDate)Within each window, perform the calculation by row. Each row is a month (set here, and in the group by statement below)ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS [Three Month Avg. Profit]This is where you specify if you want a 3 or 4 month moving average or other calculation.-- This is a 3 month moving average, the current row plus 2 preceding = 3 rows total.Side by Side Comparison of Current and Prior RecordAdd this code after your syntax before the FROM statement that adds a calculated field to show the prior month’s data for comparison purposes. Accesses data from a previous row in the same result set. LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row. This is a precursor to creating a KPI that compares the current month’s sales to the prior. For example showing a % change each month makes a nice chart! (Try to do this Tableau using table functions).--prior month revenue, LAG(SUM(SalesAmount), 1, NULL) OVER (PARTITION BY (EnglishProductSubcategoryName) ORDER BY YEAR(OrderDate), DATEPART(MONTH, OrderDate) ) AS [PriorMonth Revenue] 62230827552Line by line explanation:, LAG(SUM(SalesAmount), 1, NULL)1 is the offset value (This is used to count the number of rows back from the current row to obtain the value – here we count 1 row back).If no value is found (outside the partition) then return NULLOVER (PARTITION BY (EnglishProductSubcategoryName)OVER determines the partitioning and ordering of a rowset before the associated window function is applied. That is, the OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.PARTITION BY clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. ORDER BY YEAR(OrderDate), DATEPART(MONTH, OrderDate) ) AS [PriorMonth Revenue]ORDER BY clause determines the fields that are used for grouping for the row totals In class?? Do a 12 month comparison (year over year analysis by subcategory by region. Sounds like part of an assignment!Numeric Change from Prior Period - Add this code after the prior syntax and before the FROM to show the numerical change in sales as compared to the prior month. This change in value column is a perfect data source for an SSRS or Excel-based KPI with conditional formatting, or as a data series for a line chart., SUM(SalesAmount) - LAG(SUM(SalesAmount), 1, NULL) OVER (PARTITION BY (EnglishProductSubcategoryName) ORDER BY YEAR(OrderDate), DATEPART(MONTH, OrderDate)) AS [Month Over Month]2999276566300Line by line explanation:, SUM(SalesAmount) - LAG(SUM(SalesAmount), 1, NULL)For each row take the sum of the sales amount (grouped up by the fields specified in the GROUP BY statement) and subtract out the sum of sales for the prior period (Lag 1 month).OVER (PARTITION BY (EnglishProductSubcategoryName)Perform this subtraction within a grouping (aka a window into the data) of data here within a set of rows formed by the partition EnglishProductSubcategoryName. This means start the calculations over if the row is the first row of a sub-category.ORDER BY YEAR(OrderDate), DATEPART(MONTH, OrderDate))AS [Delta Revenue]ORDER BY clause determines grouping level for the calculations. The rows are totals for year and monthCompare this functionality to table calculations in TableauMonth Over Month Variance % Change - Add this code after the prior syntax to show the % variance change in sales as compared to the prior month. This column is perfect data sources for an SSRS or Excel-based KPI with conditional formatting. You could similarly write a year over year analysis or week over week., (SUM(SalesAmount) - LAG(SUM(SalesAmount), 1, NULL) OVER(PARTITION BY (EnglishProductSubcategoryName) ORDER BY EnglishProductSubcategoryName) ) / LAG(SUM(SalesAmount), 1, NULL) OVER (PARTITION BY (EnglishProductSubcategoryName) ORDER BY EnglishProductSubcategoryName) AS [Percent Variance]Line by line explanation:, (SUM(SalesAmount) - LAG(SUM(SalesAmount), 1, NULL)The full formula results are formatted as a percent using the FORMAT() syntax. Here the sum that was calculated for the row – based on the GROUP BY conditions is calculated then the sum for the prior row (the prior month in the window partition is subtracted from that value. Use of the format function is not always desirable. If you are needing to do a lot of work in Excel using your resultset its best to do the formatting in Excel, not in SQL.OVER(PARTITION BY (EnglishProductSubcategoryName) ORDER BY EnglishProductSubcategoryName) ) / LAG(SUM(SalesAmount), 1, NULL) OVER (PARTITION BY (EnglishProductSubcategoryName) ORDER BY EnglishProductSubcategoryName)The numerator is then divided by the total for the prior month, resulting in a percentage.--% variance from prior month = (Current month value- Previous month value)/Previous month value ) AS [Percent Variance]The FORMAT() statement is completed and the column is given a name.Note: When this query is run using Excel’s MSFT query, there is no problem with data types. However if the query is saved into a stored procedure, and called from SSRS, or used as the base query to load a powerpivot database in excel, the FORMAT() operation actually turns the column into a text column. This is bad. This error intentionally left in as a teaching case. So do your formatting in Excel not in SQL (unless you are doing a quick analysis and copying/pasting directly into Excel.) If you are building a query that will be used repeatedly as the base query for a report or PowerPivot import, then do not use FORMAT().88058385904525268351604911Breaking records into equal sized groups. You can use NTILE() to quickly break your records into groups of roughly equal size. For example an NTILE(5) would create five ‘bins’ each of which would hold roughly 20% of the records. You can place words in each row as shown in the following example or you can perhaps place a number in the cell. It is very easy for your reporting software such as Excel or SSRS to read the number in the cell and render it as an indicant (green, yellow, red stop light graphic for example). , CASE NTILE(4) OVER (PARTITION BY ([EnglishProductSubcategoryName])ORDER BY SUM(SalesAmount) DESC)WHEN 1 THEN 'Gangbusters'WHEN 2 THEN 'Great Month'WHEN 3 THEN 'Good Month'WHEN 4 THEN 'Slow Month'END AS [Sales Rating]Line by line explanation:, CASE NTILE(4) OVER (PARTITION BY ([EnglishProductSubcategoryName])This line specifies that the data is being broken into 4 categories. You can choose as many categories as you want. Four or three are good, if you will use the column as the datasource for a KPI indicant.ORDER BY SUM(SalesAmount) DESC)This line specifies that the records will be summed according to the GROUP BY expression criteria. The SUM(fieldname) totals the records based on the SalesAmount column values and sorts them in descending order - highest to lowest.WHEN 1 THEN 'Gangbusters'WHEN 2 THEN 'Great Month'WHEN 3 THEN 'Good Month'WHEN 4 THEN 'Slow Month'END This is similar to a select case. We are writing words into each row in the column. When the value placed in a cell is 1, then the record is in the bin (group) with the highest value (top 25%). Other values are written to the cell based on the other lower rankings. AS [Sales Rating]Portions quoted from you need a review of the query syntax – here is an explanation of base querySELECT [EnglishProductSubcategoryName] AS [SubCategory], YEAR(i.[OrderDate]) AS [Year]We will group up sales performance analytics based on the product category, sub-category, product, year and month, DATEPART(MONTH, i.OrderDate) AS [Month#]Use DATEPART to extract out the Month #. There are many other usages of DATEPART, DATENAME(MONTH, i.OrderDate) AS [MonthName]Use DATENAME to extract out the textual Month name (i.e., January)There are many other usages of DATENAME, COUNT(i.[OrderQuantity]) AS [Monthly Qty], , SUM(i.[SalesAmount]), 'N0') AS [Monthly Revenue], FORMAT(SUM([SalesAmount] - [TotalProductCost]) as [Profit]This will count up the order quantity column, based on the defined grouping field(s).Line 2 will total up the sales amount column based on the defined grouping field(s).Line 3 calculate the profit generated based on the defined grouping field(s).Unlike a pivot table’s calculated field, in SQL you have to define the grouping criteria (columns) before you run the queryFROM [dbo].[FactInternetSales] as iINNER JOIN [dbo].[DimProduct] as p ON p.[Productkey] = i.[Productkey]INNER JOIN [dbo].[DimProductSubcategory] as s ON s.[ProductSubcategoryKey]= p.[ProductSubcategoryKey]INNER JOIN [dbo].[DimProductcategory] as c ON c.[ProductCategoryKey]= s.[ProductCategoryKey]Here the tables that hold the fields for analysis are identified and lined together using the INNER JOIN termWHERE i.[OrderDate] BETWEEN '1/1/2006' and '12/31/2007'AND [EnglishProductCategoryName] = 'Bikes'Here we filter down the dataset’s date range. Show all the bikes data for the Internet sales channel.GROUP BY [EnglishProductCategoryName], [EnglishProductSubcategoryName], YEAR(i.[OrderDate]) , DATEPART(MONTH, i.OrderDate) , DATENAME(MONTH, i.OrderDate) Here we are grouping sales on sub-category, product, year and month. This we can see row totals for each product for each month in the 12 month time period specified. There is a SQL rule that all non-aggregate fields (ie SUM(), etc) that are specified in the SELECT statement MUST be specified in the GROUP BY statement. Notice we cannot use alias’ in the GROUP BY statementORDER BY [EnglishProductSubcategoryName], YEAR(i.[OrderDate]), DATEPART(MONTH, i.OrderDate) This is the sorting ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download