DB_Table.aggregate

aggregategroup_by columns error_on_missing_columns on_problems

Group: Calculations
Aliases: average, count, count distinct, first, group by, last, longest, maximum, mean, median, minimum, mode, percentile, shortest, standard deviation, sum, summarize, variance

Documentation

Aggregates the rows in a table using group_by columns. The columns argument specifies which additional aggregations to perform and to return.

Arguments

• group_by: Vector of column identifiers to group by. These will be included at the start of the resulting table. If no columns are specified a single row will be returned with the aggregate columns.
• columns: Vector of Aggregate_Column specifying the aggregated table. Expressions can be used within the aggregate column to perform more complicated calculations.
• error_on_missing_columns: Specifies if a missing columns in aggregates should result in an error regardless of the on_problems settings. Defaults to False, meaning that problematic aggregate will not be included in the result and the problem reported according to the on_problems setting.
• on_problems: Specifies how to handle problems if they occur, reporting them as warnings by default.

Examples

Count all the rows

       table.aggregate columns=[Aggregate_Column.Count]

Group by the Key column, count the rows

       table.aggregate ["Key"] [Aggregate_Column.Count]

Errors

• If there are no columns in the output table, a No_Output_Columns is raised as an error regardless of the problem behavior, because it is not possible to create a table without any columns.
• If a given aggregate is not supported by the backend, Unsupported_Database_Operation is reported.
• If a column index is out of range, a Missing_Input_Columns is reported according to the on_problems setting, unless error_on_missing_columns is set to True, in which case it is raised as an error. Problems resolving group_by columns are reported as dataflow errors regardless of these settings, as a missing grouping will completely change semantics of the query.
• If a column selector is given as a Text and it does not match any columns in the input table nor is it a valid expression, an Invalid_Aggregate_Column error is raised according to the on_problems settings (unless error_on_missing_columns is set to True in which case it will always be an error). Problems resolving group_by columns are reported as dataflow errors regardless of these settings, as a missing grouping will completely change semantics of the query.
• If an aggregation fails, an Invalid_Aggregation dataflow error is raised.
• The following additional problems may be reported according to the on_problems settings:
  • If there are invalid column names in the output table, a Invalid_Column_Names.
  • If there are duplicate column names in the output table, a Duplicate_Output_Column_Names.
  • If grouping on or computing the Mode on a floating point number, a Floating_Point_Equality.
  • If when concatenating values there is an quoted delimited, an Unquoted_Delimiter
  • If there are more than 10 issues with a single column, an Additional_Warnings.