Help Center/ Data Lake Insight/ SQL Syntax Reference (To Be Offline)/ Spark SQL Syntax Reference (Unavailable Soon)/ Grouping/ Expression-Based GROUP BY
Updated on 2023-11-03 GMT+08:00
View PDF

Expression-Based GROUP BY

Function

This statement is used to group a table according to expressions.

Syntax

1
2
 
SELECT attr_expr_list FROM table_reference
  GROUP BY groupby_expression [, groupby_expression, ...];

Keyword

The groupby_expression can contain a single field or multiple fields, and also can call aggregate functions or string functions.

Precautions

  • The to-be-grouped table must exist. Otherwise, an error is reported.
  • In the same single-column group, built-in functions and self-defined functions are supported in the expression in the GRUOP BY fields that must exit in attr_expr_list.

Example

To use the substr function to obtain the character string from the name field, group the student table according to the obtained character string, and return each sub character string and the number of records, run the following statement:

1
2
 
SELECT substr(name,6),count(name) FROM student
  GROUP BY substr(name,6);
Parent topic: Grouping