FROM [dbo].[DimProduct] WHERE Color <> 'silver' AND ListPrice IS NOT NULL USE [AdventureWorksDW2012]; SELECT [EnglishProductName], Color, [ListPrice] FROM [dbo].[DimProduct] WHERE Color IN ('Black', 'Red', 'Blue') AND ListPrice IS NOT NULL ORDER BY Color Rather than write the filter: WHERE Color = 'Black' OR Color = 'Red' or Color = 'Blue' You can the most excellent WHERE fieldname IN (criteria list) syntax. We will use this IN statement later in SSRS reporting. Use the IN statement to specify several criteria to be used as filters for the same field (here color). Try a different field to filter on. First look at the values for the field, then build your filter. USE [AdventureWorksDW2012]; SELECT MIN([ListPrice]) AS [Min Sales Price] , MAX([ListPrice]) AS [Max Sales Price] , AVG([ListPrice]) AS [Average Sales Price] , COUNT([ListPrice]) AS [Num Products] FROM [dbo].[DimProduct] WHERE [ProductSubcategoryKey] = 3 You can perform some interesting statistics such as the minimum, maximum values in a column, the column average value and the count of the rows For the count, you can choose any column or use COUNT(*) from fieldname. Take a look at the measures for several of the product sub-categories. Wouldn’t it be helpful to see the name of the Product Subcategory rather than just a number? USE [AdventureWorksDW2012]; SELECT DISTINCT PromotionKey FROM FactResellerSales ORDER BY PromotionKey Sometimes you have a large table and you want to quickly pull out a list of unique values from it. You can use the DISTINCT keyword to do the trick. Can you retrieve unique values for other fields?
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.