Databases Reference
In-Depth Information
Aggregations over the Aggr output
It's also possible to perform additional aggregations over the result set that the
Aggr
function outputs. Let's look at the following example:
To build upon the insight gained from the previous example, suppose we want to use
a text object to present the maximum, minimum, and average number of interstate
routes served by all carriers; our starting point would, again, be the following chart:
Since the chart is sorted by number of interstate routes in descending order, we
can easily see that the maximum value, regarding number of interstate routes per
carrier, belongs to
Delta Air Lines Inc.
and is
1,145
. To get the minimum value, we
would sort the table in ascending order. To get the average value, however, it gets
kind of tricky. Let's use the
Aggr
function to display all three values at once. Follow
this procedure:
1.
Click on the
Create Text Object
button in the design toolbar.
2.
The
New Text Object
window will appear, with the
General
tab initially
active. The text we want displayed by the object is entered into the
Text
field.
If we want to define the display text based on an expression,
the
Text
definition must begin with an equal to sign.
On the
Text
field, type the following expression:
=Max(Aggr(Count(DISTINCT [From - To State Code]), [Carrier Name]))
Notice that the
Aggr
function part in the earlier expression is the same as that
which we used in the previous example to create the chart's dimension. We
are now adding the
Max
aggregation function to obtain the largest number
from the resulting list of values.
