Database Reference
In-Depth Information
6.1.13 Aggregation Functions
As can be expected, MDX provides many aggregation functions. We have
seen already an example of the
SUM
and
AVG
functions. Other functions like
MEDIAN
,
MAX
,
MIN
,
VAR
,and
STDDEV
compute, respectively, the median,
maximum, minimum, variance, and standard deviation of tuples in a set based
on a numeric value. For example, the following query analyzes each product
category to see the total, maximum, minimum, and average sales amount for
a 1-month period in 1997:
WITH MEMBER Measures.[Maximum Sales] AS
MAX(DESCENDANTS([Order Date].Calendar.Year.[1997],
[Order Date].Calendar.Month), Measures.[Sales Amount])
MEMBER Measures.[Minimum Sales] AS
MIN(DESCENDANTS([Order Date].Calendar.Year.[1997],
[Order Date].Calendar.Month), Measures.[Sales Amount])
MEMBER Measures.[Average Sales] AS
AVG(DESCENDANTS([Order Date].Calendar.Year.[1997],
[Order Date].Calendar.Month), Measures.[Sales Amount])
SELECT
{
[Sales Amount], [Maximum Sales],
[Minimum Sales], [Average Sales]
ON COLUMNS,
Product.Categories.Category.MEMBERS ON ROWS
FROM Sales
}
The result of the query is as follows:
Sales Amount Maximum Sales Minimum Sales Average Sales
Beverages
$237,203.91
$21,817.76
$2,109.84
$7,652.65
Condiments
$91,528.81
$5,629.70
$1,252.33
$3,842.09
Confections
$162,443.91
$11,538.61
$2,174.89
$6,798.83
Dairy Products $221,157.31
$12,992.48
$5,584.84
$9,119.26
Grains/Cereals
$80,870.58
$6,012.65
$1,891.00
$4,193.64
Meat/Poultry
$139,428.18
$14,110.16
$1,029.00
$6,217.45
Produce
$90,216.14
$12,157.90
$1,650.00
$4,429.52
Seafood
$122,307.02
$8,448.86
$1,587.11
$5,263.19
Our next query computes the maximum sales by category as well as the
month in which they occurred:
WITH MEMBER Measures.[Maximum Sales] AS
MAX(DESCENDANTS([Order Date].Calendar.Year.[1997],
[Order Date].Calendar.Month), Measures.[Sales Amount])
MEMBER Measures.[Maximum Period] AS
TOPCOUNT(DESCENDANTS([Order Date].Calendar.Year.[1997],
[Order Date].Calendar.Month), 1,
Measures.[Sales Amount]).ITEM(0).NAME
SELECT
{
[Maximum Sales], [Maximum Period]
}
ON COLUMNS,
Product.Categories.Category.MEMBERS ON ROWS
FROM Sales
Search WWH ::
Custom Search