COUNT(object/property/group_id[, group][, group])
COUNT_DISTINCT(property/group_id[, group][, group])
AVG(property[, group][, group])
SUM(property[, group][, group])
MIN(property[, group][, group])
MAX(property[, group][, group])
Aggregate functions are a way of boiling many records down into a single value. They can be applied to any property at the group member level (not datapoint).
Aggregate functions include three parts:
- The function (see below)
- The property
- The group(s) over which you want the value to be calculated
The function can be:
- COUNT - A count of datapoints
- COUNT_DISTINCT - A count of unique values
- AVG - The arithmetic mean of the property (NULLs excluded)
- SUM - The sum of the property
- MIN - The smallest value for the property
- MAX - The largest value for the property
Caveats:
- In the case of COUNT, a property or group_id may or may not be used, depending on the value you’re looking for
- Timestamps cannot be averaged or summed
- In the case of a RESULT report type, the group can be left off
- At the lowest level of a CROSSTAB OR NESTED report type, the group must be left off
Examples:
{|RESULT|}<table class='data'>
<thead>
<tr>
<th>Name</th>
<th>Count</th>
<th>Average Score</th>
<th>Highest Score</th>
<th>Lowest Score</th>
<th>Total Points</th>
</tr>
</thead>
<tbody>
{|LOOP|RESULT.MONTH_OF_YEAR}
<tr>
<th>{MONTH_OF_YEAR.NAME}</th>
#the count of datapoints for the month
<td>{COUNT(MONTH_OF_YEAR)}</td>
#the average of the property SCORE for the month
<td>{AVG(MONTH_OF_YEAR.SCORE)}</td>
#the highest value of the property SCORE for the month
<td>{MAX(MONTH_OF_YEAR.SCORE)}</td>
#the lowest value of the property SCORE for the month
<td>{MIN(MONTH_OF_YEAR.SCORE)}</td>
#the sum of the property POINTS for the month
<td>{SUM(MONTH_OF_YEAR.POINTS)}</td>
</tr>
{/|LOOP|}
</tbody>
</table>
{/|RESULT|}
{|CROSSTAB|}{|LOOP|CROSSTAB.YEAR(SORT=ORDER_NUM)}
{|SHOWIF|LOOP.ISFIRST}
<table class='data'>
<thead>
<tr>
<th rowspan="2">Year</th>
{|LOOP|YEAR.MONTH_OF_YEAR(SORT=ORDER_NUM)}
<th colspan="2">{MONTH_OF_YEAR.NAME}</th>
{/|LOOP|}
<th colspan="2">Year Total/th>
</tr>
<tr>
{|LOOP|YEAR.MONTH_OF_YEAR(SORT=ORDER_NUM)}
<th>Count</th>
<th>Average Score</th>
{/|LOOP|}
<th>Count</th>
<th>Average Score</th>
</tr>
</thead>
<tbody>
{/|SHOWIF|}
<tr>
<th>{YEAR.NAME}</th>
{|LOOP|YEAR.MONTH_OF_YEAR}
#the count of datapoints for this month in this year
<td>{COUNT(MONTH_OF_YEAR)}</td>
#the average of the property SCORE for this month in this year
<td>{AVG(MONTH_OF_YEAR.SCORE)}</td>
{/|LOOP|}
#the count of datapoints for this year
<td>{COUNT(YEAR, YEAR)}</td>
#the average of the property SCORE for this year
<td>{AVG(YEAR.SCORE, YEAR)}</td>
</tr>
{|VANISHIF|LOOP.HASNEXT}
<tr>
<th>Month Total</th>
{|LOOP|YEAR.MONTH_OF_YEAR}
#the count of datapoints for this month for all years
<td>{COUNT(MONTH_OF_YEAR, MONTH_OF_YEAR)}</td>
#the average of the property SCORE for this month for all years
<td>{AVG(MONTH_OF_YEAR.SCORE, MONTH_OF_YEAR)}</td>
{/|LOOP|}
#the count of datapoints for all years
<td>{COUNT(YEAR, OVERALL)}</td>
#the average of the property SCORE for all years
<td>{AVG(YEAR.SCORE, OVERALL)}</td>
</tr>
</tbody>
</table>
{/|VANISHIF|}
{/|LOOP|}
{/|CROSSTAB|}
Aggregates may also be used within aggregates. This is useful for getting an average count, for example, or a maximum average. It can also be useful in a situation where limits or filters have been applied and you only want totals to reflect the records displayed.
{|RESULT|}<table class='data'>
<thead>
<tr>
<th>Name</th>
<th>Count</th>
<th>Average Score</th>
<th>Total Points</th>
</tr>
</thead>
<tbody>
{|LOOP|RESULT.MONTH_OF_YEAR}
<tr>
<th>{MONTH_OF_YEAR.NAME}</th>
#the count of datapoints for the month
<td>{COUNT(MONTH_OF_YEAR)}</td>
#the average of the property SCORE for the month
<td>{AVG(MONTH_OF_YEAR.SCORE)}</td>
#the sum of the property POINTS for the month
<td>{SUM(MONTH_OF_YEAR.POINTS)}</td>
</tr>
{/|LOOP|}
<tr>
<th>Summary</th>
#the average count of datapoints per month
<td>{AVG(COUNT(MONTH_OF_YEAR), OVERALL)}</td>
#the highest average of the property SCORE for any month
<td>{MAX(AVG(MONTH_OF_YEAR.SCORE), OVERALL)}</td>
#the sum of the property POINTS for all month
<td>{SUM(SUM(MONTH_OF_YEAR.POINTS), OVERALL)}</td>
</tr>
</tbody>
</table>
{/|RESULT|}
{|CROSSTAB|}{|LOOP|CROSSTAB.YEAR(SORT=ORDER_NUM)}
{|SHOWIF|LOOP.ISFIRST}
<table class='data'>
<thead>
<tr>
<th rowspan="2">Year</th>
{|LOOP|YEAR.MONTH_OF_YEAR(SORT=ORDER_NUM)}
<th colspan="2">{MONTH_OF_YEAR.NAME}</th>
{/|LOOP|}
<th colspan="2">Year Total</th>
</tr>
<tr>
{|LOOP|YEAR.MONTH_OF_YEAR(SORT=ORDER_NUM)}
<th>Count</th>
<th>Average Score</th>
{/|LOOP|}
<th>Maximum Count</th>
<th>Maximum Average Score</th>
</tr>
</thead>
<tbody>
{/|SHOWIF|}
<tr>
<th>{YEAR.NAME}</th>
{|LOOP|YEAR.MONTH_OF_YEAR}
#the count of datapoints for this month in this year
<td>{COUNT(MONTH_OF_YEAR)}</td>
#the average of the property SCORE for this month in this year
<td>{AVG(MONTH_OF_YEAR.SCORE)}</td>
{|VANISHIF|LOOP.HASNEXT}
#the highest count of datapoints for a month for this year
<td>{MAX(COUNT(MONTH_OF_YEAR), YEAR)}</td>
#the highest average of the property SCORE for a month for this year
<td>{MAX(AVG(MONTH_OF_YEAR.SCORE), YEAR)}</td>
{/|VANISHIF|}
{/|LOOP|}
</tr>
{|VANISHIF|LOOP.HASNEXT}
<tr>
<th>Month Total</th>
{|LOOP|YEAR.MONTH_OF_YEAR}
#the average count of datapoints for this month for all years
<td>{AVG(COUNT(MONTH_OF_YEAR), MONTH_OF_YEAR)}</td>
#the highest average of the property SCORE for this month for all years
<td>{MAX(AVG(MONTH_OF_YEAR.PERCENT_SCORE), MONTH_OF_YEAR)}</td>
{/|LOOP|}
#the average count of datapoints per year for all years
<td>{AVG(COUNT(YEAR, YEAR), OVERALL)}</td>
#the highest average of the property SCORE per year for all years
<td>{MAX(AVG(YEAR.SCORE, YEAR), OVERALL)}</td>
</tr>
</tbody>
</table>
{/|VANISHIF|}
{/|LOOP|}
{/|CROSSTAB|}
{|RESULT|}{|LOOP|RESULT.MONTH_OF_YEAR}
{|SHOWIF|LOOP.ISFIRST}
{/|SHOWIF|}
#count of shops for the current month
#count of shops with a non-NULL location for the current month
#count of unique locations with shops in the current month
#average number of shops per location for current month
{|VANISHIF|LOOP.HASNEXT}
#total number of shops
#total number of shops with a non-NULL location for the current month
#total number of unique locations with shops
#note that this is NOT the sum of values for each month
#average number of shops per location overall
{|SORT_TOGGLE|MONTH_OF_YEAR.ORDER_NUM}Name{/|SORT_TOGGLE|} {|SORT_TOGGLE|COUNT(MONTH_OF_YEAR)}Count{/|SORT_TOGGLE|} {|SORT_TOGGLE|COUNT(MONTH_OF_YEAR.CLIENT_10_LEVEL_1_ID)}Count Shops with Locations{/|SORT_TOGGLE|} {|SORT_TOGGLE|COUNT_DISTINCT(MONTH_OF_YEAR.CLIENT_10_LEVEL_1_ID)}Count Distinct Locations{/|SORT_TOGGLE|} Avg Shops per Loc
{MONTH_OF_YEAR.NAME} {COUNT(MONTH_OF_YEAR)} {COUNT(MONTH_OF_YEAR.CLIENT_10_LEVEL_1_ID)} {COUNT_DISTINCT(MONTH_OF_YEAR.CLIENT_10_LEVEL_1_ID)} {CALC(COUNT(MONTH_OF_YEAR) * 1.0 / COUNT_DISTINCT(MONTH_OF_YEAR.CLIENT_10_LEVEL_1_ID)).ROUND(2)}
Total {COUNT(MONTH_OF_YEAR,OVERALL)} {COUNT(MONTH_OF_YEAR.CLIENT_10_LEVEL_1_ID,OVERALL)} {COUNT_DISTINCT(MONTH_OF_YEAR.CLIENT_10_LEVEL_1_ID,OVERALL)} {CALC(COUNT(MONTH_OF_YEAR,OVERALL) * 1.0 / COUNT_DISTINCT(MONTH_OF_YEAR.CLIENT_10_LEVEL_1_ID,OVERALL)).ROUND(2)}
{/|VANISHIF|}
{/|LOOP|}
{/|RESULT|}
{|CROSSTAB|}{|LOOP|CROSSTAB.YEAR(SORT=ORDER_NUM)}
{|SHOWIF|LOOP.ISFIRST}
{|LOOP|YEAR.MONTH_OF_YEAR(SORT=ORDER_NUM)}
{/|LOOP|}
{/|SHOWIF|}
{|LOOP|YEAR.MONTH_OF_YEAR}
#number of unique locations with shops for this month and year combo
{/|LOOP|}
#average number of locations with shops for the year
{|VANISHIF|LOOP.HASNEXT}
{|LOOP|YEAR.MONTH_OF_YEAR}
#average number of locations with shops for the month in all years
{/|LOOP|}
Name {MONTH_OF_YEAR.NAME} Avg for Year
{YEAR.NAME} {COUNT_DISTINCT(MONTH_OF_YEAR.CLIENT_10_LEVEL_1_ID)} {AVG(COUNT_DISTINCT(YEAR.CLIENT_10_LEVEL_1_ID), YEAR).ROUND(2)}
Total {AVG(COUNT_DISTINCT(MONTH_OF_YEAR.CLIENT_10_LEVEL_1_ID), MONTH_OF_YEAR).ROUND(2)}
{/|VANISHIF|}
{/|LOOP|}
{/|CROSSTAB|}