GROUP BY¶
The GROUP BY clause can be used to aggregate data.
OpenCypher Compatibility¶
This topic applies to native nGQL only.
You can also use the count() function to aggregate data.
nebula> MATCH (v:player)<-[:follow]-(:player) RETURN v.name AS Name, count(*) as cnt ORDER BY cnt DESC;
+----------------------+-----+
| Name | cnt |
+----------------------+-----+
| "Tim Duncan" | 10 |
| "LeBron James" | 6 |
| "Tony Parker" | 5 |
| "Chris Paul" | 4 |
| "Manu Ginobili" | 4 |
+----------------------+-----+
...
Syntax¶
The GROUP BY clause groups the rows with the same value. Then operations such as counting, sorting, and calculation can be applied.
The GROUP BY clause works after the pipe symbol (|) and before a YIELD clause.
| GROUP BY <var> YIELD <var>, <aggregation_function(var)>
The aggregation_function() function supports avg(), sum(), max(), min(), count(), collect(), and std().
Examples¶
The following statement finds all the vertices connected directly to vertex "player100", groups the result set by player names, and counts how many times the name shows up in the result set.
nebula> GO FROM "player100" OVER follow BIDIRECT \
YIELD properties($$).name as Name \
| GROUP BY $-.Name \
YIELD $-.Name as Player, count(*) AS Name_Count;
+---------------------+------------+
| Player | Name_Count |
+---------------------+------------+
| "Shaquille O'Neal" | 1 |
| "Tiago Splitter" | 1 |
| "Manu Ginobili" | 2 |
| "Boris Diaw" | 1 |
| "LaMarcus Aldridge" | 1 |
| "Tony Parker" | 2 |
| "Marco Belinelli" | 1 |
| "Dejounte Murray" | 1 |
| "Danny Green" | 1 |
| "Aron Baynes" | 1 |
+---------------------+------------+
Group and calculate with functions¶
The following statement finds all the vertices connected directly to vertex "player100", groups the result set by source vertices, and returns the sum of degree values.
nebula> GO FROM "player100" OVER follow \
YIELD src(edge) AS player, properties(edge).degree AS degree \
| GROUP BY $-.player \
YIELD sum($-.degree);
+----------------+
| sum($-.degree) |
+----------------+
| 190 |
+----------------+
For more information about the sum() function, see Built-in math functions.