cancel
Showing results for 
Search instead for 
Did you mean: 

Create new relation based on sum of property values in Cypher neo4j?

apremgeorge
Node Clone

@michael.hunger
I have 4 person nodes belong to same team where team name is a property in node, one person has a relation to a new node community as the score is > 100, as shown

CREATE (Paul:Person {id:'1', name:'Paul', Team:'T1', Joined:datetime('2020-03-04T23:13:49.990000000Z'), Score: 111})
CREATE (Jean:Person {id:'2', name:'Jean', Team:'T1', Joined:datetime('2020-03-03T23:13:49.990000000Z'), Score: 88})
CREATE (Dan:Person {id:'3', name:'Dan', Team:'T1', Joined:datetime('2020-03-02T23:13:49.990000000Z'), Score: 45})
CREATE (Mike:Person {id:'4', name:'Mike', Team:'T1', Joined:datetime('2020-03-01T23:13:49.990000000Z'), Score: 36})

CREATE (Community:Teams {id:'11', name:'Community', street:'2626 Wilkinson Court', address:'San Bernardino, CA 92410'})

CREATE (Paul)-[:SCORE_AB100]->(Community)

RETURN *

2X_d_d030654ae6c14e29391f1a9b9f8b463a83549cf7.png
Each person node has a property named Score, i want to create a new relation between nodes Jean,Dan who joined in last two days to the node Community, if the sum of Scores of 3 nodes(Paul,Jean,Dan) is greater than 200.

To return these nodes

MATCH (p:Person{Team: 'T1'})
WHERE datetime(p.Joined) > datetime('2020-03-01T23:14:49.990000000Z')
MATCH (p1:Person{Team: 'T1'})-[r:SCORE_AB100]-(t:Teams)
RETURN p,r,t,p1

2X_7_715b8c1b37a3c65fee594d25ad1e9c7c856db292.png

To return the score and sum

MATCH (p:Person{Team: 'T1'})
WHERE datetime(p.Joined) > datetime('2020-03-01T23:14:49.990000000Z')
MATCH (p1:Person{Team: 'T1'})-[r:SCORE_AB100]-(t:Teams)
RETURN collect(p.Score),sum(p.Score)

2X_1_1f6054809f94ab8e7adeb7674a0372d38f85e6f9.png

As the sum is above 200, i want to create a new relation [:SCORE_AB200] between Jean to Community and Dan to Community, and also paul to community another relation.
2X_a_a2011c5edf82e805c8c762ab5d327b0a9f432882.png

I tried using sum(p.Score) > 200 in WHERE its showing error

1 ACCEPTED SOLUTION
2 REPLIES 2

apremgeorge
Node Clone

i tried something like this

MATCH (p:Person),(t:Teams)
WHERE datetime(p.Joined) > datetime('2020-03-01T23:14:49.990000000Z')
WITH sum(p.Score) as total, collect (id(p)) as per
MATCH (p1:Person) WHERE id(p1) in per AND total > 200
CREATE (p1)-[r:SCORE_AB200]->(t:Teams)
RETURN per,total

but not working