COUNT subqueries
A COUNT
subquery can be used to count the number of rows returned by the subquery.
Example graph
The following graph is used for the examples below:
To recreate the graph, run the following query against an empty Neo4j database:
CREATE
(andy:Swedish:Person {name: 'Andy', age: 36}),
(timothy:Person {name: 'Timothy', nickname: 'Tim', age: 25}),
(peter:Person {name: 'Peter', nickname: 'Pete', age: 35}),
(andy)-[:HAS_DOG {since: 2016}]->(:Dog {name:'Andy'}),
(timothy)-[:HAS_CAT {since: 2019}]->(:Cat {name:'Mittens'}),
(fido:Dog {name:'Fido'})<-[:HAS_DOG {since: 2010}]-(peter)-[:HAS_DOG {since: 2018}]->(:Dog {name:'Ozzy'}),
(fido)-[:HAS_TOY]->(:Toy{name:'Banana'})
Simple COUNT
subquery
Variables introduced by the outside scope can be used in the COUNT
subquery without importing them.
In this regard, COUNT
subqueries are different from CALL
subqueries, which do require importing.
The following query exemplifies this and outputs the owners of more than one dog:
MATCH (person:Person)
WHERE COUNT { (person)-[:HAS_DOG]->(:Dog) } > 1
RETURN person.name AS name
name |
---|
|
Rows: 1 |
COUNT
subquery with WHERE
clause
A WHERE
clause can be used inside the COUNT
pattern.
Variables introduced by the MATCH
clause and the outside scope can be used in this scope.
MATCH (person:Person)
WHERE COUNT {
(person)-[:HAS_DOG]->(dog:Dog)
WHERE person.name = dog.name
} = 1
RETURN person.name AS name
name |
---|
|
Rows: 1 |
COUNT
subquery with a UNION
COUNT
can be used with a UNION
clause. If the UNION
clause is distinct, the RETURN
clause is required.
UNION ALL
clauses do not require the RETURN
clause. However, it is worth noting that if one branch has a RETURN
clause, then all require one.
The below example shows the count of pets each person has by using a UNION
clause:
MATCH (person:Person)
RETURN
person.name AS name,
COUNT {
MATCH (person)-[:HAS_DOG]->(dog:Dog)
RETURN dog.name AS petName
UNION
MATCH (person)-[:HAS_CAT]->(cat:Cat)
RETURN cat.name AS petName
} AS numPets
name | numPets |
---|---|
|
|
|
|
|
|
Rows: 3 |
COUNT
subquery with WITH
Variables from the outside scope are visible for the entire subquery, even when using a WITH
clause.
To avoid confusion, shadowing of these variables is not allowed.
An outside scope variable is shadowed when a newly introduced variable within the inner scope is defined with the same variable.
In the example below, the outer variable name
is shadowed and will therefore throw an error.
WITH 'Peter' as name
MATCH (person:Person {name: name})
WHERE COUNT {
WITH "Ozzy" AS name
MATCH (person)-[:HAS_DOG]->(d:Dog)
WHERE d.name = name
} = 1
RETURN person.name AS name
The variable `name` is shadowing a variable with the same name from the outer scope and needs to be renamed (line 4, column 20 (offset: 90))
New variables can be introduced into the subquery, as long as they use a different identifier.
In the example below, a WITH
clause introduces a new variable.
Note that the outer scope variable person
referenced in the main query is still available after the WITH
clause.
MATCH (person:Person)
WHERE COUNT {
WITH "Ozzy" AS dogName
MATCH (person)-[:HAS_DOG]->(d:Dog)
WHERE d.name = dogName
} = 1
RETURN person.name AS name
name |
---|
|
Rows: 1 |
Using COUNT
subqueries inside other clauses
COUNT
can be used in any position in a query, with the exception of administration commands, where it is restricted.
See a few examples below:
Using COUNT
in RETURN
MATCH (person:Person)
RETURN person.name, COUNT { (person)-[:HAS_DOG]->(:Dog) } as howManyDogs
person.name | howManyDogs |
---|---|
|
|
|
|
|
|
Rows: 3 |
Using COUNT
in SET
MATCH (person:Person) WHERE person.name ="Andy"
SET person.howManyDogs = COUNT { (person)-[:HAS_DOG]->(:Dog) }
RETURN person.howManyDogs as howManyDogs
howManyDogs |
---|
|
Rows: 1 |
Using COUNT
in CASE
MATCH (person:Person)
RETURN
CASE
WHEN COUNT { (person)-[:HAS_DOG]->(:Dog) } > 1 THEN "Doglover " + person.name
ELSE person.name
END AS result
result |
---|
|
|
|
Rows: 3 |
Using COUNT
as a grouping key
The following query groups all persons by how many dogs they own, and then calculates the average age for each group.
MATCH (person:Person)
RETURN COUNT { (person)-[:HAS_DOG]->(:Dog) } AS numDogs,
avg(person.age) AS averageAge
ORDER BY numDogs
numDogs | averageAge |
---|---|
|
|
|
|
|
|
Rows: 3 |
COUNT
subquery with RETURN
COUNT
subqueries do not require a RETURN
clause at the end of the subquery.
If one is present, it does not need to be aliased.
This is a difference compared to CALL
subqueries.
Any variables returned in a COUNT
subquery will not be available after the subquery.
MATCH (person:Person)
WHERE COUNT {
MATCH (person)-[:HAS_DOG]->(:Dog)
RETURN person.name
} = 1
RETURN person.name AS name
name |
---|
|
Rows: 1 |
Rules
The following is true for COUNT
subqueries:
-
Any non-writing query is allowed.
-
The final
RETURN
clause may be omitted, as any variable defined within the subquery will not be available outside of the expression, even if a finalRETURN
clause is used. One exception to this is that for aDISTINCT UNION
clause, theRETURN
clause is still mandatory. -
The
MATCH
keyword can be omitted in subqueries in cases where theCOUNT
consists of only a pattern and an optionalWHERE
clause. -
A
COUNT
subquery can appear anywhere in a query that an expression is valid. -
Any variable that is defined in the outside scope can be referenced inside the
COUNT
subquery’s own scope. -
Variables introduced inside the
COUNT
subquery are not part of the outside scope and therefore cannot be accessed on the outside.