CODEWITHSUNDEEP
neo4jcypher
LMHull
LMHull

Reputation: 77

Neo4J query using calculations within RETURN statement

I have a simple model:

(teacher)-[:HAD]->(contract)-[:WITH]-(school)

One teacher can have more than one contract with the same school or other schools. Now, a property on contract is {leadership: 'Senior Leader'}, and I am trying to query all Senior Leaders to find out how many schools they had contract with. 

//FIND ALL HEADS AND NUMBER OF SCHOOLS THEY WENT TO
MATCH (t:Teacher)
WHERE (t)-[:HAD]->(:Contract {leadership: 'Head'})
MATCH (t)-[:HAD]->(:Contract)-[:WITH]->(s:School)
RETURN t,count(distinct(s)) as nr_of_schools
ORDER BY nr_of_schools DESC

I want to use nr_of_schools in a calculation:

toFloat(no_of_schools) / count(distinct(t))*100
ORDER BY toFloat(no_of_schools) / count(distinct(t))*100 DESC

in essence I am after finding that 70% of Head Teachers went to 1 school, 20% of Head Teachers went to 2 schools and the rest to 3 schools. I thought I can use no_of_schools as a parameter but I don't think that is possible.

Upvotes: 1

Views: 255

Answers (1)

František Hartman
František Hartman

Reputation: 15086

You can use WITH for intermediate calculations:

//FIND ALL HEADS AND NUMBER OF SCHOOLS THEY WENT TO
MATCH (t:Teacher)
WHERE (t)-[:HAD]->(:Contract {leadership: 'Head'})
MATCH (t)-[:HAD]->(:Contract)-[:WITH]->(s:School)
WITH t,count(distinct(s)) as no_of_schools
RETURN toFloat(no_of_schools) / count(distinct(t))*100 AS ratio
ORDER BY ratio DESC

If you want to return t with the ratio you need to rewrite the query a bit - you can't return both variable and aggregation on the variable - RETURN t,count(t) is not possible.

MATCH (t:Teacher)
WITH count(t) as tCount // don't need distinct here, but wouldn't hurt
MATCH (t:Teacher)
WHERE (t)-[:HAD]->(:Contract {leadership: 'Head'})
MATCH (t)-[:HAD]->(:Contract)-[:WITH]->(s:School)
WITH t,tCount,count(distinct(s)) as no_of_schools
RETURN toFloat(no_of_schools) / tCount*100 AS ratio
ORDER BY ratio DESC

Upvotes: 1

Related Questions