Overview
The GROUP BY
clause allows you to specify the set of grouping keys to be used during grouping on the final table.
<return statement> ::=
"RETURN" [ "DISTINCT" ] { <"*"> | <return item list> }
[ <group by clause> ]
<group by clause> ::= "GROUP BY" <grouping key list>
<grouping key list> ::=
<grouping key> [ { "," <grouping key> }... ]
<grouping key> ::= <binding variable>
Details
- Each grouping key must be a binding variable. If the desired grouping key is not already an existing binding variable, you have two options:
- Renaming using
AS
: If the grouping key is part of the final result table, you can rename the corresponding column using theAS
keyword. - Using
LET
: Alternatively, you can use theLET
statement to define a new variable for the grouping key before theRETURN
statement, ensuring it is available for grouping.
- Renaming using
- After grouping, only one record in each group will be returned.
- When grouping is involved, any aggregation operation in the
RETURN
statement is applied to each group after the grouping is done.
Example Graph
The following examples run against this graph:
To create this graph, run the following query against an empty graph:
INSERT (alex:Student {_id: 's1', name: 'Alex', gender: 'male'}),
(susan:Student {_id: 's2', name: 'Susan', gender: 'female'}),
(alex2:Student {_id: 's3', name: 'Alex', gender: 'female'}),
(art:Course {_id: 'c1', name: 'Art', credit: 13}),
(literature:Course {_id: 'c2', name: 'Literature', credit: 15}),
(alex)-[:Take {year: 2024, term: 'Spring'}]->(art),
(alex2)-[:Take {year: 2023, term: 'Fall'}]->(art),
(susan)-[:Take {year: 2023, term: 'Fall'}]->(art),
(susan)-[:Take {year: 2023, term: 'Spring'}]->(literature)
Grouping by One Key
MATCH (:Student)->(c:Course)
RETURN c GROUP BY c
Result: c
_id | _uuid | schema | values |
---|---|---|---|
c1 | Sys-gen | Course | {name: "Art", credit: 13} |
c2 | Sys-gen | Course | {name: "Literature", credit: 15} |
Grouping by Column Alias
In this query, the grouping key Name
is the column alias of n.name
:
MATCH (n:Student)
RETURN n.name AS Name GROUP BY Name
Result:
Name |
---|
Alex |
Susan |
The following query throws syntax error as the grouping key n.name
is not a binding variable:
MATCH (n:Student)
RETURN n.name GROUP BY n.name
Grouping by Variables Defined by LET
In this query, the grouping key Gender
is defined by the LET
statement:
MATCH (n:Student)
LET Gender = n.gender
RETURN n GROUP BY Gender
Result:
n |
---|
(:Student {_id: "s1", gender: "male", name: "Alex"}) |
(:Student {_id: "s3", gender: "female", name: "Alex"}) |
The following query throws syntax error as the grouping key n.gender
is not a binding variable:
MATCH (n:Student)
RETURN n GROUP BY n.gender
Grouping with Aggregation
MATCH (n:Student)
RETURN n.name AS Name, count(n) GROUP BY Name
Result:
Name | count(n) |
---|---|
Alex | 2 |
Susan | 1 |
Grouping by Multiple Keys
MATCH ({_id: "c1"})<-[e:Take]-()
RETURN e.year AS Y, e.term AS T GROUP BY Y, T
Result:
Year | Term |
---|---|
2023 | Fall |
2024 | Spring |