Querying Data Warehouses - Data Warehouse Systems: Design and Implementation - page 200

aggregates each measure using the aggregation operator specified for each
measure. Thus, for measures Unit Price and Discount , the average is used,
while for the other measures, the sum is applied. The result of the query is
given below. We can see the values for each one of the top three cities, the
aggregated values for the top three cities, and the aggregated values of the
other cities.

Unit Price Quantity Discount Sales Amount Freight Sales Count

Cunewalde $101.46 3,616 21.40% $103,597.43 $4,999.77 77

Boise $90.90 4,809 32.41% $102,253.85 $6,570.58 113

Graz $88.00 4,045 23.57% $93,349.45 $5,725.79 92

Top 3 Cities $95.46 12,470 26.69% $299,200.73 $17,296.14 282

Other Cities $38.68 33,918 0.95% $845,955.13 $41,291.35 1,649

All Customers $134.14 46,388 27.64% $1,145,155.86 $58,587.49 1,931

Other functions exist for top filter processing. The TOPPERCENT and
TOPSUM functions return the top elements whose cumulative total is at
least a specified percentage or a specified value, respectively. For example,
the next query displays the list of cities whose sales count accounts for 30%
of all the sales.

SELECT Measures.[Sales Amount] ON COLUMNS,
{ TOPPERCENT(Customer.Geography.City.MEMBERS, 30,

Measures.[Sales Amount]), Customer.Geography.[All] } ON ROWS

FROM Sales

The result of the query is as follows:

Sales Amount
Cunewalde $103,597.43
Boise $102,253.85
Graz $93,349.45
London $51,169.01
Albuquerque $49,290.08
All Customers $1,145,155.86

As can be seen, the sum of the sales of the cities in the answer amounts to
34% of the total sales amount.
There is also an analogous series of BOTTOM functions, returning the
bottom items in a list. For example, in the above query we could use the
BOTTOMSUM function to obtain the bottom cities whose cumulative sales
amount is less than, say, $10,000.