15.8. Aggregation

15.8.1. COUNT
15.8.2. Count nodes
15.8.3. Group Count Relationship Types
15.8.4. Count entities
15.8.5. Count non null values
15.8.6. SUM
15.8.7. AVG
15.8.8. MAX
15.8.9. MIN
15.8.10. COLLECT
15.8.11. DISTINCT

To calculate aggregated data, Cypher offers aggregation, much like SQL’s GROUP BY. If any aggregation functions are found in the RETURN statement, all the columns without aggregating functions are used as the grouping key.

Graph

cypher-aggregation-graph.svg

15.8.1. COUNT

COUNT is used to count the number of rows. COUNT can be used in two forms - COUNT(*) which just counts the number of matching rows, and COUNT(<identifier>), which counts the number of non-null values in <identifier>.

15.8.2. Count nodes

To count the number of nodes, for example the number of nodes connected to one node, you can use count(*).

Query

START n=node(2)
MATCH (n)-->(x)
RETURN n, count(*)

The start node and the count of related nodes.

Result

ncount(*)
1 rows, 1 ms

Node[2]{name->"A",property->13}

3


15.8.3. Group Count Relationship Types

To count the groups of relationship types, return the types and count them with count(*).

Query

START n=node(2)
MATCH (n)-[r]->()
RETURN type(r), count(*)

The relationship types and their group count.

Result

TYPE(r)count(*)
1 rows, 1 ms

"KNOWS"

3


15.8.4. Count entities

Instead of counting the number of results with count(*), it might be more expressive to include the name of the identifier you care about.

Query

START n=node(2)
MATCH (n)-->(x)
RETURN count(x)

The number of connected nodes from the start node.

Result

count(x)
1 rows, 0 ms

3


15.8.5. Count non null values

You can count the non-null values by using count(<identifier>).

Query

START n=node(2,3,4,1)
RETURN count(n.property?)

The count of related nodes.

Result

count(n.property)
1 rows, 0 ms

3


15.8.6. SUM

The SUM aggregation function simply sums all the numeric values it encounters. Null values are silently dropped. This is an example of how you can use SUM.

Query

START n=node(2,3,4)
RETURN sum(n.property)

The sum of all the values in the property property.

Result

sum(n.property)
1 rows, 0 ms

90


15.8.7. AVG

AVG calculates the average of a numeric column.

Query

START n=node(2,3,4)
RETURN avg(n.property)

The average of all the values in the property property.

Result

avg(n.property)
1 rows, 0 ms

30.0


15.8.8. MAX

MAX find the largets value in a numeric column.

Query

START n=node(2,3,4)
RETURN max(n.property)

The largest of all the values in the property property.

Result

max(n.property)
1 rows, 1 ms

44


15.8.9. MIN

MIN takes a numeric property as input, and returns the smallest value in that column.

Query

START n=node(2,3,4)
RETURN min(n.property)

The smallest of all the values in the property property.

Result

min(n.property)
1 rows, 1 ms

13


15.8.10. COLLECT

COLLECT collects all the values into a list.

Query

START n=node(2,3,4)
RETURN collect(n.property)

Returns a single row, with all the values collected.

Result

collect(n.property)
1 rows, 0 ms

List(13, 33, 44)


15.8.11. DISTINCT

All aggregation functions also take DISTINCT modifier, which removes duplicates from the values. So, to count the number of unique eye colours from nodes related to a, this query can be used:

Query

START a=node(2)
MATCH a-->b
RETURN count(distinct b.eyes)

Returns the number of eye colours.

Result

count(distinct b.eyes)
1 rows, 1 ms

2