Prev |  | Next

## 16.7. Aggregation

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

### 16.7.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>`.

### 16.7.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

n | count(*)
--- | ---
1 rows, 1 ms
`Node[2]{name->"A",property->13}` | `3`

### 16.7.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`

### 16.7.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, 1 ms
`3`

### 16.7.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`

### 16.7.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`

### 16.7.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`

### 16.7.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, 0 ms
`44`

### 16.7.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, 0 ms
`13`

### 16.7.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)`

### 16.7.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`

Copyright © 2011 Neo Technology

Prev | Up | Next
| Home |