GROUP BY clause

The GROUP BY clause groups rows based on the specified columns. Rows with the same values in those columns are placed into the same group, and aggregate calculations are then performed for each group.

Limitations

The GROUP BY clause can be used only after a RETURN statement that contains at least one aggregate function.

Syntax

diagramsource

group_by_clause

group_by_clause ::=
    'GROUP BY' ( '()' | variable_value_expression ( ',' variable_value_expression )* )

Parameters

variable_value_expression

The variable that specifies the column to group by. For more information, see Variable value expression.

()

An indication to group all rows as a single group.

Examples

Group by one column

'''
FOR i IN LIST[1,2,3,4,2,2] 
RETURN i, i+1 AS i_1, i+2 AS i_2, count(*) 
GROUP BY i
'''

The preceding query groups the numbers in the list by value, counts the occurrences of each unique value, and then performs the calculations.

Return:

+---+-----+-----+----------+
| i | i_1 | i_2 | count(*) |
+---+-----+-----+----------+
| 1 | 2   | 3   | 1        |
| 2 | 3   | 4   | 3        |
| 3 | 4   | 5   | 1        |
| 4 | 5   | 6   | 1        |
+---+-----+-----+----------+

Group by one aliased column

'''
TABLE users TYPED TABLE {id INT, name STRING} =
    (199, "Julie Kavner"),
    (198, "Tom Cruise"),
    (198, "Amy Hasegawa")
FOR r IN users
RETURN r.id AS id, count(*) AS cnt
GROUP BY id
'''

The preceding query groups all rows that have the same id value and counts the number of rows in each group.

Return:

+-----+-----+
| id  | cnt |
+-----+-----+
| 199 | 1   |
| 198 | 2   |
+-----+-----+

Group by multiple columns

'''
TABLE users TYPED TABLE {id INT, name STRING, age INT} =
    (199, "Julie Kavner", 30),
    (198, "Tom Cruise", 25),
    (198, "Amy Hasegawa", 25),
    (199, "Chris Evans", 30)
FOR r IN users
RETURN r.id AS id, r.age AS age, count(*) AS cnt
GROUP BY id, age
'''

The preceding query performs the following operations:

1. Reads each row in the users table.
2. Groups rows by the combination of id and age, which means two rows belong to the same group only when both values are identical.
3. Uses count(*) to count the number of rows in each group.
4. Returns the grouped id, age, and aggregated count cnt.

Return:

+-----+-----+-----+
| id  | age | cnt |
+-----+-----+-----+
| 199 | 30  | 2   |
| 198 | 25  | 2   |
+-----+-----+-----+

Group all rows into one group

'''
FOR i in LIST[1,2,3,4,2,2] 
RETURN count(*), sum(i) 
GROUP BY ()
'''

The preceding query groups all rows into the same group and performs the aggregation calculation.

Return:

+----------+--------+
| count(*) | sum(i) |
+----------+--------+
| 6        | 14     |
+----------+--------+