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