Introduction to SQL - Microsoft Business Intelligence Tools for Excel Analysts - page 181

Select da.AccountType, sum(ff.Amount) AmountTotal, avg(ff.Amount)
AmountAverage
From dbo.DimAccount da
Join dbo.FactFinance ff
On da.AccountKey = ff.AccountKey
Group By da.AccountType
Having sum(ff.Amount)>=10
Order By da.AccountType Asc, sum(ff.Amount) Desc

You can specify the Order By clause by referencing the specific column names or the column ordi-
nal as it is listed in the Select clause:

Select da.AccountType, sum(ff.Amount) AmountTotal, avg(ff.Amount)
AmountAverage
From dbo.DimAccount da
Join dbo.FactFinance ff
On da.AccountKey = ff.AccountKey
Group By da.AccountType
Having sum(ff.Amount)>=10
Order By 1 Asc, 2 Desc

Selecting Distinct records
The Distinct keyword allows you to return the unique occurrences of the values in your record set
without using a Group By clause. This example uses a Distinct keyword in the Select
statement.

Select Distinct da.AccountType
From dbo.DimAccount da
Join dbo.FactFinance ff
On da.AccountKey = ff.AccountKey

Without the Distinct keyword, AccountType is repeated for every row in FactFinance (that
is related to DimAccount ). However, with Distinct you would only get the unique occurrences
of AccountType .

A common use of the Distinct keyword is to validate the unique key of a dataset or a table
to make sure your understanding of the data matches what exists in your database. Take the
DimDate table in the AdventureWorksDW , for example. FullDateAlternateKey is
defined, according to the name of the column, as the alternate key for that table. Therefore,
running the distinct set of FullDateAlternateKey produces a record count equal to
Count(*) on the table (see Figure 9-2). Examining the results in the Results pane of the
Query window, you can validate this is true.

Tip