GROUP BY Statement
Grouping can be used to group values by one or more expressions involving columns.
Grouping requires a list of grouping expressions and the list of aggregation columns.
A group by expression can be a column name or an expression involving multiple columns, and an optional list of aggregation functions after the COLUMNS keyword. All the group by expressions and the aggregates listed after the COLUMNS keyword are displayed by the query.
<groupByStatement> ::= "GROUP BY" <columnExpression> ( "," <columnExpression> )* )?(COLUMNS <aggregationFunction> ("," <aggregationFunction> )* )?;
The following aggregation functions are supported:
|
•
|
COUNT(*): Count all the rows. |
|
•
|
SUM(column): Sum of all values from the column. Supports numeric types (Integer, Long, Double). |
|
•
|
AVG(column): Provide average value for the column. Supports numeric types (Integer, Long, Double). |
|
•
|
MIN(column): Smallest value of the column. Supports all data types that can be ordered (Integer, Long, Double, Timestamp, String). |
|
•
|
MAX(column): Largest value of the column. Supports all data types that can be ordered (Integer, Long, Double, Timestamp, String). |
Examples
USE TS_hawk_bwce_getactivitystats_error_count | GROUP BY activity_name | COLUMNS activity_name, Count(*)
|
The result is the count of all the metric values for each activity name. |
USE TS_hawk_bwce_getactivitystats_error_count | GROUP BY activity_name | COLUMNS activity_name, avg(value)
|
The result is the average of all the metric values for each activity name. |