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

This SQL statement returns only records with amounts greater than or equal to 10.
Select da.AccountType, da.Operator, ff.Amount
From dbo.DimAccount da
Join dbo.FactFinance ff
On da.AccountKey = ff.AccountKey
Where ff.Amount >=10
Grouping
Grouping is a useful SQL feature for analytics. It allows you to aggregate your result set and perform
advanced filtering. You perform grouping on your result set by using the Group By clause in your
Select statement. The Group By clause is often used with an Aggregation function and/or a
Having clause. Here's a basic Group By 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
This Select statement returns a list of values in AccountType along with the total amount for
each value. This example uses the Sum (sum of records) and Avg (average of records) Aggregation
functions. SQL includes several Aggregation functions that can be used with a Group By clause,
including Count (count of records), Min (minimum value), and Max (maximum value).
You can set an advanced filter on a query using the Having 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
This Select statement returns the AccountType records that have a sum of an amount greater
than or equal to 10. The difference between this example and a filter applied using the Where clause
is that the filter is applied after the aggregation.
The Order By clause
The Order By clause allows you to set the order of the returned records in your result set. The default
sort order in SQL is ascending; you can change that to descending. Here's a basic Order By example: