13.4. Aggregation

13.4.1. Introduction
13.4.2. COUNT
13.4.3. Count nodes
13.4.4. Group Count Relationship Types
13.4.5. Count entities
13.4.6. Count non-null values
13.4.7. SUM
13.4.8. AVG
13.4.9. PERCENTILE_DISC
13.4.10. PERCENTILE_CONT
13.4.11. MAX
13.4.12. MIN
13.4.13. COLLECT
13.4.14. DISTINCT

13.4.1. Introduction

To calculate aggregated data, Cypher offers aggregation, much like SQL’s GROUP BY.

Aggregate functions take multiple input values and calculate an aggregated value from them. Examples are AVG that calculate the average of multiple numeric values, or MIN that finds the smallest numeric value in a set of values.

Aggregation can be done over all the matching sub graphs, or it can be further divided by introducing key values. These are non-aggregate expressions, that are used to group the values going into the aggregate functions.

So, if the return statement looks something like this:

RETURN n, count(*)

We have two return expressions — n, and count(*). The first, n, is no aggregate function, and so it will be the grouping key. The latter, count(*) is an aggregate expression. So the matching subgraphs will be divided into different buckets, depending on the grouping key. The aggregate function will then run on these buckets, calculating the aggregate values.

If you want to use aggregations to sort your result set, the aggregation must be included in the RETURN to be used in your ORDER BY.

The last piece of the puzzle is the DISTINCT keyword. It is used to make all values unique before running them through an aggregate function.

An example might be helpful:

Query. 

MATCH me:Person-->friend:Person-->friend_of_friend:Person
WHERE me.name = 'A'
RETURN count(DISTINCT friend_of_friend), count(friend_of_friend)

In this example we are trying to find all our friends of friends, and count them. The first aggregate function, count(distinct friend_of_friend), will only see a friend_of_friend once — DISTINCT removes the duplicates. The latter aggregate function, count(friend_of_friend), might very well see the same friend_of_friend multiple times. Since there is no real data in this case, an empty result is returned. See the sections below for real data.

Result

count(distinct friend_of_friend)count(friend_of_friend)
1 row

0

0


The following examples are assuming the example graph structure below.

Figure 13.4. Graph


13.4.2. 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>.

13.4.3. Count nodes

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

Query. 

MATCH (n)-->(x)
WHERE n.name = 'A'
RETURN n, count(*)

This returns the start node and the count of related nodes.

Result

ncount(*)
1 row

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

3


13.4.4. Group Count Relationship Types

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

Query. 

MATCH (n)-[r]->()
WHERE n.name='A'
RETURN type(r), count(*)

The relationship types and their group count is returned by the query.

Result

type(r)count(*)
1 row

"KNOWS"

3


13.4.5. 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. 

MATCH (n)-->(x)
WHERE n.name = 'A'
RETURN count(x)

The example query returns the number of connected nodes from the start node.

Result

count(x)
1 row

3


13.4.6. Count non-null values

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

Query. 

MATCH n:Person
RETURN count(n.property?)

The count of related nodes with the property property set is returned by the query.

Result

count(n.property?)
1 row

3


13.4.7. SUM

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

Query. 

MATCH n:Person
WHERE HAS (n.property)
RETURN sum(n.property)

This returns the sum of all the values in the property property.

Result

sum(n.property)
1 row

90


13.4.8. AVG

AVG calculates the average of a numeric column.

Query. 

MATCH n:Person
WHERE HAS (n.property)
RETURN avg(n.property)

The average of all the values in the property property is returned by the example query.

Result

avg(n.property)
1 row

30.0


13.4.9. PERCENTILE_DISC

PERCENTILE_DISC calculates the percentile of a given value over a group, with a percentile from 0.0 to 1.0. It uses a rounding method, returning the nearest value to the percentile. For interpolated values, see PERCENTILE_CONT.

Query. 

MATCH n:Person
WHERE HAS (n.property)
RETURN percentile_disc(n.property, 0.5)

The 50th percentile of the values in the property property is returned by the example query. In this case, 0.5 is the median, or 50th percentile.

Result

percentile_disc(n.property, 0.5)
1 row

33


13.4.10. PERCENTILE_CONT

PERCENTILE_CONT calculates the percentile of a given value over a group, with a percentile from 0.0 to 1.0. It uses a linear interpolation method, calculating a weighted average between two values, if the desired percentile lies between them. For nearest values using a rounding method, see PERCENTILE_DISC.

Query. 

MATCH n:Person
WHERE HAS (n.property)
RETURN percentile_cont(n.property, 0.4)

The 40th percentile of the values in the property property is returned by the example query, calculated with a weighted average.

Result

percentile_cont(n.property, 0.4)
1 row

29.0


13.4.11. MAX

MAX find the largets value in a numeric column.

Query. 

MATCH n:Person
WHERE HAS (n.property)
RETURN max(n.property)

The largest of all the values in the property property is returned.

Result

max(n.property)
1 row

44


13.4.12. MIN

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

Query. 

MATCH n:Person
WHERE HAS (n.property)
RETURN min(n.property)

This returns the smallest of all the values in the property property.

Result

min(n.property)
1 row

13


13.4.13. COLLECT

COLLECT collects all the values into a list. It will ignore null values,

Query. 

MATCH n:Person
RETURN collect(n.property?)

Returns a single row, with all the values collected.

Result

collect(n.property?)
1 row

[13,33,44]


13.4.14. DISTINCT

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

Query. 

MATCH a:Person-->b
WHERE a.name = 'A'
RETURN count(DISTINCT b.eyes)

Returns the number of eye colors.

Result

count(distinct b.eyes)
1 row

2