To summarize query results, you can apply an aggregation to:
- A specific field or calculation
- Rows or columns in a crosstab layout
When you aggregate a value in a crosstab layout, you can select whether to show its grand total for the rows or columns.
Apply an aggregation to a field or calculation
- From the Field Properties panel for the field or calculation, select the aggregation to apply.
- For a calculation, to include each unique item only once, select Use distinct aggregation.
- Click Save.
When you compose the query's SQL statement, apply an aggregate function to the field, such as:
SUM(field_name) FROM source
Add an aggregation value to crosstab results
To summarize a field in crosstab query results, you can apply an aggregation value and select whether to display grand totals for row or columns.
- From the Crosstab panel, drag the field to aggregate under Values, and select the formula to apply.
- To display the aggregation as a grand total, in Totals, select whether to show its total for rows or columns.
- Click Save.
Aggregation values
The aggregations available depend on:
- The field's data type, such as Text or Date
- Whether you create the query in Builder or SQL
- Whether you aggregate a field or crosstab results
| Aggregation | Data types | Details |
|---|---|---|
Count of, COUNT, or DISTINCTCOUNT |
All | Totals how many items are in the field. To count each item only once in a crosstab layout:
|
Min of or MIN |
Date, Decimal, Integer, or Timestamp | Displays the minimum value—the lowest numerical value or earliest time value—of the items in the field |
Max of or MAX |
Date, Decimal, Integer, or Timestamp | Displays the maximum value—the highest numerical value or latest time value—of the items in the field |
Sum of or SUM |
Decimal or Integer | Totals the numerical values of the items in the field |
Average of or AVERAGE |
Decimal or Integer | Totals the numerical values of the items in the field, and then divides that total by the number of items to calculate the mean—or average—value |
PRODUCT |
Decimal or Integer | Multiplies the numerical values of the items in the field
Note: This aggregation is only available for a crosstab layout in SQL. |
PERCENT |
Decimal or Integer | Totals the numerical values of the items in the field, and displays each item's value as a percentage of that total
Note: This aggregation is only available for a crosstab layout in SQL. |
Percent of column or PERCENTOFCOLUMN |
Decimal or Integer | For a crosstab layout, displays each item's value as a percentage of the grand total of each column |
Percent of row or PERCENTOFROW |
Decimal or Integer | For a crosstab layout, displays each item's value as a percentage of the grand total of each row |
DIFFERENCE |
Decimal or Integer | For a crosstab layout, subtracts each item's value from the previous item's, such as to show change over time
Note: This aggregation is only available for a crosstab layout in SQL. To ease comparison, apply a
|
PERCENTDIFFERENCE |
Decimal or Integer | For a crosstab layout, displays each item's value as a percentage of the previous item's value, such as to show change over time
Note: This aggregation is only available for a crosstab layout in SQL. |
STDEVP or STDEVS |
Decimal or Integer | For a crosstab layout, calculates the standard deviation of the numerical values of the items in the field, to measure how widely the values vary from their average.
Note: This aggregation is only available for a crosstab layout in SQL. |
Median of or MEDIAN |
Decimal or Integer | For a crosstab layout, calculates the "typical" numerical value of the items in the field—50% of the items are greater than this value, the other 50% are less than it. |
Running totals of or RUNNINGTOTALS |
Decimal or Integer | For a crosstab layout, calculates a cumulative sum of the numerical values of the items in the field. For example, apply this aggregation to track the total income quarter-over-quarter. |